In MariaDB, SYSDATE() is a built-in date and time function that returns the current date and time.

It returns the time that the function was executed (which could be different to the time that the statement was executed).

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:

SYSDATE([precision])

Where precision is an optional argument that specifies the microsecond precision.

Example

Here’s an example:

SELECT SYSDATE();

Result:

+---------------------+
| SYSDATE()           |
+---------------------+
| 2021-05-27 08:40:41 |
+---------------------+

Numeric Context

When SYSDATE() is used in a numeric context, the result is in YYYYMMDDHHMMSS.uuuuuu format.

Example:

SELECT
    SYSDATE(),
    SYSDATE() + 0;

Result:

+---------------------+----------------+
| SYSDATE()           | SYSDATE() + 0  |
+---------------------+----------------+
| 2021-05-27 08:41:19 | 20210527084119 |
+---------------------+----------------+

Precision

Here’s an example of specifying microsecond precision:

SELECT
    SYSDATE(6),
    SYSDATE(6) + 0;

Result:

+----------------------------+-----------------------+
| SYSDATE(6)                 | SYSDATE(6) + 0        |
+----------------------------+-----------------------+
| 2021-05-27 08:41:44.561665 | 20210527084144.562263 |
+----------------------------+-----------------------+

The maximum value for the microsecond precision is 6. Here’s what happens when a higher number is passed for the precision:

SELECT SYSDATE(12);

Result:

ERROR 1426 (42000): Too big precision 12 specified for 'sysdate'. Maximum is 6

SYSDATE() vs NOW()

Although SYSDATE() is similar to the NOW() function, there are differences.

One of the main differences is that SYSDATE() returns the time that it executes, whereas NOW() returns the time that the statement started executing. When run within a stored procedure or trigger, NOW() will return the time that the stored procedure or trigger started executing.

Here’s an example that demonstrates this difference:

SELECT 
    NOW(),
    SYSDATE(),
    SLEEP(5),
    NOW(),
    SYSDATE();

Result (using vertical output):

    NOW(): 2021-05-27 08:47:06
SYSDATE(): 2021-05-27 08:47:06
 SLEEP(5): 0
    NOW(): 2021-05-27 08:47:06
SYSDATE(): 2021-05-27 08:47:11

In this case we used the SLEEP() function to pause processing for five seconds before the second lot of function calls.

We can see that the second time SYSDATE() was invoked, its return value was different to the first time. NOW() on the other hand, returned the same value both times.

There are a few other things to be mindful of when using SYSDATE(). For example, SET TIMESTAMP doesn’t affect it (whereas it does affect the NOW() function). Also, SYSDATE() is unsafe for replication if statement-based binary logging is used. However, there are ways to deal with this, if required. See the MariaDB documentation for more information.