In MariaDB, LOCALTIME
and LOCALTIME()
are synonyms for NOW()
.
The NOW()
function is a built-in date and time function that returns the current date and time.
The time is returned in either 'YYYY-MM-DD HH:MM:SS'
or YYYYMMDDHHMMSS.uuuuuu
format, depending on whether the function is being used in a string or numeric context.
Syntax
LOCALTIME()
can be used in the following ways:
LOCALTIME
LOCALTIME([precision])
Where precision
is the microsecond precision.
You can alternatively call NOW()
like this:
NOW([precision])
Example
Here’s an example:
SELECT
LOCALTIME,
LOCALTIME(),
NOW();
Result:
+---------------------+---------------------+---------------------+ | LOCALTIME | LOCALTIME() | NOW() | +---------------------+---------------------+---------------------+ | 2021-05-10 09:06:38 | 2021-05-10 09:06:38 | 2021-05-10 09:06:38 | +---------------------+---------------------+---------------------+
We can see that all three return the same result.
Numeric Context
When used in a numeric context, the resulting time is in YYYYMMDDHHMMSS.uuuuuu
format.
Example:
SELECT
LOCALTIME,
LOCALTIME + 0,
LOCALTIME() + 0;
Result:
+---------------------+----------------+-----------------+ | LOCALTIME | LOCALTIME + 0 | LOCALTIME() + 0 | +---------------------+----------------+-----------------+ | 2021-05-10 09:06:54 | 20210510090654 | 20210510090654 | +---------------------+----------------+-----------------+
Precision
When used with the LOCALTIME([precision])
syntax, you can specify the microsecond precision for the result.
Example:
SELECT
LOCALTIME(6),
LOCALTIME(6) + 0;
Result:
+----------------------------+-----------------------+ | LOCALTIME(6) | LOCALTIME(6) + 0 | +----------------------------+-----------------------+ | 2021-05-10 09:07:09.717031 | 20210510090709.717031 | +----------------------------+-----------------------+
The maximum value for the microsecond precision is 6. Here’s what happens when a higher number is passed for the precision:
SELECT LOCALTIME(7);
Result:
ERROR 1426 (42000): Too big precision 7 specified for 'current_timestamp'. Maximum is 6
Adding to the Current Timestamp
There are many ways to perform arithmetic on datetime values in MariaDB.
Here’s an example of using the addition operator (+
) to add 2 days to the date:
SELECT
LOCALTIME,
LOCALTIME + INTERVAL 2 DAY;
Result:
+---------------------+----------------------------+ | LOCALTIME | LOCALTIME + INTERVAL 2 DAY | +---------------------+----------------------------+ | 2021-05-10 09:07:42 | 2021-05-12 09:07:42 | +---------------------+----------------------------+
Also see functions like DATE_ADD()
and ADDDATE()
for an alternative way to add to the current date.
Subtracting from the Current Date
Here’s an example of using the subtraction operator (-
) to subtract 10 days from the current date:
SELECT
LOCALTIME,
LOCALTIME - INTERVAL 10 DAY;
Result:
+---------------------+-----------------------------+ | LOCALTIME | LOCALTIME - INTERVAL 10 DAY | +---------------------+-----------------------------+ | 2021-05-10 09:07:58 | 2021-04-30 09:07:58 | +---------------------+-----------------------------+
See functions like DATE_SUB()
and SUBDATE()
for an alternative way to subtract from the current date.