In MariaDB, NOW()
is a built-in date and time function that returns the current date and time.
The return value is returned in 'YYYY-MM-DD HH:MM:SS'
or YYYYMMDDHHMMSS.uuuuuu
format, depending on whether the function is used in a string or numeric context.
The return value is expressed in the current time zone.
You also have the option of specifying the microsecond precision.
Syntax
The syntax goes like this:
NOW([precision])
Where precision
is an optional argument that specifies the microsecond precision.
It also has the following synonyms:
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])
LOCALTIME
LOCALTIME([precision])
LOCALTIMESTAMP
LOCALTIMESTAMP([precision])
Example
Here’s an example:
SELECT NOW();
Result:
+---------------------+ | NOW() | +---------------------+ | 2021-05-09 10:12:39 | +---------------------+
Numeric Context
When NOW()
is used in a numeric context, the result is in YYYYMMDDHHMMSS.uuuuuu
format.
Example:
SELECT
NOW(),
NOW() + 0;
Result:
+---------------------+----------------+ | NOW() | NOW() + 0 | +---------------------+----------------+ | 2021-05-09 10:13:10 | 20210509101310 | +---------------------+----------------+
Precision
Here’s an example of specifying microsecond precision:
SELECT
NOW(6),
NOW(6) + 0;
Result:
+----------------------------+-----------------------+ | NOW(6) | NOW(6) + 0 | +----------------------------+-----------------------+ | 2021-05-09 10:13:30.619522 | 20210509101330.619522 | +----------------------------+-----------------------+
The maximum value for the microsecond precision is 6. Here’s what happens when a higher number is passed for the precision:
SELECT NOW(12);
Result:
ERROR 1426 (42000): Too big precision 12 specified for 'current_timestamp'. Maximum is 6
Synonyms
As seen in the syntax above, there are a bunch of synonyms for NOW()
.
Here’s an example that uses the various synonyms:
SELECT
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP(),
LOCALTIME,
LOCALTIME(),
LOCALTIMESTAMP,
LOCALTIMESTAMP();
Result (using vertical output):
CURRENT_TIMESTAMP: 2021-05-09 10:24:53 CURRENT_TIMESTAMP(): 2021-05-09 10:24:53 LOCALTIME: 2021-05-09 10:24:53 LOCALTIME(): 2021-05-09 10:24:53 LOCALTIMESTAMP: 2021-05-09 10:24:53 LOCALTIMESTAMP(): 2021-05-09 10:24:53
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
NOW(),
NOW() + INTERVAL 2 DAY;
Result:
+---------------------+------------------------+ | NOW() | NOW() + INTERVAL 2 DAY | +---------------------+------------------------+ | 2021-05-09 10:17:39 | 2021-05-11 10:17:39 | +---------------------+------------------------+
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
NOW(),
NOW() - INTERVAL 10 DAY;
Result:
+---------------------+-------------------------+ | NOW() | NOW() - INTERVAL 10 DAY | +---------------------+-------------------------+ | 2021-05-09 10:19:35 | 2021-04-29 10:19:35 | +---------------------+-------------------------+
See functions like DATE_SUB()
and SUBDATE()
for an alternative way to subtract from the current date.