UNIX_TIMESTAMP()¶

Function Description¶

UNIX_TIMESTAMP() Returns the number of seconds from 1970-01-01 00:00:00 UTC to the current time.

UNIX_TIMESTAMP(date) Returns the value of the parameter to 1970-01-01 00:00:00 UTC to date The number of seconds specified.

If the date is out of range passed to UNIX_TIMESTAMP()`, it returns 0. IfdateisNULL, thenNULL`` is returned.

If no parameter is given or the parameter does not contain a fractional second part, the return value is an integer; if the parameter given contains a fractional second part, the return value is DECIMAL.

Function Syntax¶

> UNIX_TIMESTAMP([date])

Parameter definition¶

Parameters	Description
date	Optional parameters. The date parameter is a legal date expression. The date parameter can be a `DATE`, `DATETIME` or `TIMESTAMP` string, or a numeric in YYMMDD, YYMMDDhhmmss, YYYYMMDD, or YYYYMMDDhhmmss format. If the date parameter contains the time part, it selectively contains the fractional part of the second. When the date parameter is `TIMESTAMP`, `UNIX_TIMESTAMP()` directly returns the internal timestamp value without the implicit string-to-Unix-timestamp conversion.

Parameters

Description

date

Optional parameters. The date parameter is a legal date expression.
The date parameter can be a DATE, DATETIME or TIMESTAMP string, or a numeric in YYMMDD, YYMMDDhhmmss, YYYYMMDD, or YYYYMMDDhhmmss format. If the date parameter contains the time part, it selectively contains the fractional part of the second.
When the date parameter is TIMESTAMP, UNIX_TIMESTAMP() directly returns the internal timestamp value without the implicit string-to-Unix-timestamp conversion.

Conversion between values of non-UTC time zones and Unix timestamp values¶

If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between a non-UTC (Coordinated Universal Time) time zone value and a Unix timestamp value, the conversion is lossy because the mapping is not one-to-one in both directions. For example, UNIX_TIMESTAMP() can map two different values in non-UTC time zones to the same Unix timestamp value due to conventions such as daylight saving time (DST). FROM_UNIXTIME() maps this value back to only one of the original values. The following example, that is, use different values in the MET time zone:

mysql> SET time_zone = 'MET';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+-------------------------------------------+
| unix_timestamp(2005-03-27 03:00:00) |
+-------------------------------------------+
| 1111885200 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+-------------------------------------------+
| unix_timestamp(2005-03-27 02:00:00) |
+-------------------------------------------+
| 1111885200 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(1111885200);
+--------------------------------+
| from_unixtime(1111885200) |
+--------------------------------+
| 2005-03-27 03:00:00 |
+--------------------------------+
1 row in set (0.00 sec)

Example¶

mysql> SELECT UNIX_TIMESTAMP("2016-07-11");
+----------------------------------+
| unix_timestamp(2016-07-11) |
+----------------------------------+
| 1468188000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
+-------------------------------------------+
| unix_timestamp(2015-11-13 10:20:19) |
+-------------------------------------------+
| 1447406419 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
+---------------------------------------------------+
| unix_timestamp(2015-11-13 10:20:19.012) |
+---------------------------------------------------+
| 1447406419.012000 |
+---------------------------------------------------+
1 row in set (0.00 sec)

limit¶

Currently, the date format only supports the data formats of yyyy-mm-dd and yyyymmdd.