MariaDB CURRENT_TIMESTAMP() Explained

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.