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.