In MariaDB, CURRENT_TIMESTAMP
and CURRENT_TIMESTAMP()
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
CURRENT_TIMESTAMP()
can be used in the following ways:
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])
Where precision
is the microsecond precision.
You can alternatively call NOW()
like this:
NOW([precision])
Example
Here’s an example:
SELECT
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP(),
NOW();
Result:
+---------------------+---------------------+---------------------+ | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP() | NOW() | +---------------------+---------------------+---------------------+ | 2021-05-09 15:46:30 | 2021-05-09 15:46:30 | 2021-05-09 15:46:30 | +---------------------+---------------------+---------------------+
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
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP + 0,
CURRENT_TIMESTAMP() + 0;
Result:
+---------------------+-----------------------+-------------------------+ | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP + 0 | CURRENT_TIMESTAMP() + 0 | +---------------------+-----------------------+-------------------------+ | 2021-05-09 15:47:12 | 20210509154712 | 20210509154712 | +---------------------+-----------------------+-------------------------+
Precision
When used with the CURRENT_TIMESTAMP([precision])
syntax, you can specify the microsecond precision for the result.
Example:
SELECT
CURRENT_TIMESTAMP(6),
CURRENT_TIMESTAMP(6) + 0;
Result:
+----------------------------+--------------------------+ | CURRENT_TIMESTAMP(6) | CURRENT_TIMESTAMP(6) + 0 | +----------------------------+--------------------------+ | 2021-05-09 15:47:39.508987 | 20210509154739.508987 | +----------------------------+--------------------------+
The maximum value for the microsecond precision is 6. Here’s what happens when a higher number is passed for the precision:
SELECT CURRENT_TIMESTAMP(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
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP + INTERVAL 2 DAY;
Result:
+---------------------+------------------------------------+ | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP + INTERVAL 2 DAY | +---------------------+------------------------------------+ | 2021-05-09 15:49:01 | 2021-05-11 15:49:01 | +---------------------+------------------------------------+
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
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP - INTERVAL 10 DAY;
Result:
+---------------------+-------------------------------------+ | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP - INTERVAL 10 DAY | +---------------------+-------------------------------------+ | 2021-05-09 15:49:28 | 2021-04-29 15:49:28 | +---------------------+-------------------------------------+
See functions like DATE_SUB()
and SUBDATE()
for an alternative way to subtract from the current date.