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.

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.