UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since 1970-01-01 00:00:00
UTC) as an unsigned integer. If UNIX_TIMESTAMP()
is called with a date argument, it returns the value of the argument as seconds since 1970-01-01 00:00:00
UTC. date may be a DATE string, aDATETIME string, a TIMESTAMP, or a number in
the format YYMMDD
or YYYYMMDD
. The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone as described in time zones.
The inverse function of UNIX_TIMESTAMP()
is FROM_UNIXTIME()
UNIX_TIMESTAMP()
supports microseconds.
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15
. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL
being returned. Use DATETIME as a storage type if you require dates beyond this.
Timestamps in MariaDB have a maximum value of 2147483647, equivalent to 2038-01-19 05:14:07
. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL
being returned. Use DATETIME as a storage type if you require dates beyond this.
Returns NULL
for wrong arguments to UNIX_TIMESTAMP()
.
SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1269711082 |
+------------------+
SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
+---------------------------------------+
| UNIX_TIMESTAMP('2007-11-30 10:30:19') |
+---------------------------------------+
| 1196436619.000000 |
+---------------------------------------+
SELECT UNIX_TIMESTAMP("2007-11-30 10:30:19.123456");
+----------------------------------------------+
| unix_timestamp("2007-11-30 10:30:19.123456") |
+----------------------------------------------+
| 1196411419.123456 |
+----------------------------------------------+
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-30 10:30:19'));
+------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-30 10:30:19')) |
+------------------------------------------------------+
| 2007-11-30 10:30:19.000000 |
+------------------------------------------------------+
SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP('2007-11-30 10:30:19')));
+-------------------------------------------------------------+
| FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP('2007-11-30 10:30:19'))) |
+-------------------------------------------------------------+
| 2007-11-30 10:30:19 |
+-------------------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql