How UNIX_TIMESTAMP() Works in MariaDB

In MariaDB, UNIX_TIMESTAMP() is a built-in date and time function that returns a Unix timestamp, based on its argument (or lack of argument).

It works like this:

When called without an argument, it returns a Unix timestamp (seconds since ‘1970-01-01 00:00:00’ UTC) as an unsigned integer.
When called with an argument, it returns the value of the argument as seconds since ‘1970-01-01 00:00:00’ UTC.

The inverse function of UNIX_TIMESTAMP() is FROM_UNIXTIME().

Syntax

UNIX_TIMESTAMP() can be called in the following two ways:

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)

Where date is a date string, a datetime string, a timestamp, or a number in the format YYMMDD or YYYYMMDD.

Example – Without an Argument

Here’s an example of calling UNIX_TIMESTAMP() without an argument:

SELECT UNIX_TIMESTAMP();

Result:

+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1622502492 |
+------------------+

This tells us that when I ran that statement, 1622502492 seconds had passed since 1970-01-01 00:00:00.

Example – With an Argument

Here’s an example with an argument:

SELECT UNIX_TIMESTAMP('1970-01-02');

Result:

+------------------------------+
| UNIX_TIMESTAMP('1970-01-02') |
+------------------------------+
|                        50400 |
+------------------------------+

In the following example, I call UNIX_TIMESTAMP() twice; once with no argument, and once with NOW() as the argument.

SELECT 
    UNIX_TIMESTAMP(),
    UNIX_TIMESTAMP(NOW());

Result:

+------------------+-----------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) |
+------------------+-----------------------+
|       1622502678 |            1622502678 |
+------------------+-----------------------+

Datetime String

In the above example, NOW() returns a datetime value.

In this example, I explicitly provide a datetime string:

SELECT UNIX_TIMESTAMP('2020-10-30 10:23:47');

Result:

+---------------------------------------+
| UNIX_TIMESTAMP('2020-10-30 10:23:47') |
+---------------------------------------+
|                            1604017427 |
+---------------------------------------+

Microseconds

UNIX_TIMESTAMP() supports microseconds:

SELECT UNIX_TIMESTAMP('2020-10-30 10:23:47.123456');

Result:

+----------------------------------------------+
| UNIX_TIMESTAMP('2020-10-30 10:23:47.123456') |
+----------------------------------------------+
|                            1604017427.123456 |
+----------------------------------------------+

Numeric Dates

Numeric dates are supported:

SELECT UNIX_TIMESTAMP(20201030);

Result:

+--------------------------+
| UNIX_TIMESTAMP(20201030) |
+--------------------------+
|               1603980000 |
+--------------------------+

Invalid Argument

When passed any invalid arguments, UNIX_TIMESTAMP() returns null with a warning:

SELECT UNIX_TIMESTAMP('Homer');

Result:

+-------------------------+
| UNIX_TIMESTAMP('Homer') |
+-------------------------+
|                    NULL |
+-------------------------+
1 row in set, 1 warning (0.001 sec)

Check the warning:

SHOW WARNINGS;

Result:

+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1292 | Incorrect time value: 'Homer' |
+---------+------+-------------------------------+

Too Many Arguments

Calling UNIX_TIMESTAMP() with too many arguments results in an error:

SELECT UNIX_TIMESTAMP('1970-01-02', '1970-01-03');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'UNIX_TIMESTAMP'