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'