UNIX_TIMESTAMP

Description

Converts a Date or Datetime type to a UNIX timestamp.

If no argument is provided, it converts the current time to a timestamp.

The argument must be of Date or Datetime type.

For the format specification, refer to the format description of the date_format function.

This function is affected by the time zone.

Sytax

UNIX_TIMESTAMP([DATETIME date[, STRING fmt]])

Parameter

Paramters	Description
`<date>`	The datetime value to be converted is of type `datetime` or `date` type, with a convertible range from '1970-01-01 00:00:01.000000 UTC' to '3001-01-19 03:14:07.999999 UTC'.
`<fmt>`	The 'date' parameter refers to the specific part that needs to be converted into a timestamp, and it is a parameter of type string. If this parameter is provided, only the part matching the format will be converted into a timestamp.

Return value

Returns two types based on the input:

If the input date(only datetime type have the scale not zero) scale is not 0 or a format parameter is provided, returns a timestamp of type Decimal with a maximum precision of six decimal places.
If the input datetime scale is 0 and no format parameter is provided, returns a timestamp of type INT.
For times before '1970-01-01 00:00:01.000000 UTC' or after '3001-01-19 03:14:07.999999 UTC', the function returns 0.

Returns NULL if any argument is NULL.

Examples

 
-- All the following results are returned in the UTC time zone
 
set time_zone= 'UTC';
 
------Displays the timestamp of the current time
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1753933330 |
+------------------+
 
---Input a datetime to display its timestamp
mysql> select unix_timestamp('2007-11-30 10:30:19');
+---------------------------------------+
| unix_timestamp('2007-11-30 10:30:19') |
+---------------------------------------+
|                            1196389819 |
+---------------------------------------+
 
---Matches the format to display the timestamp corresponding to the given datetime
mysql> select unix_timestamp('2007-11-30 10:30-19', '%Y-%m-%d %H:%i-%s');
+------------------------------------------------------------+
| unix_timestamp('2007-11-30 10:30-19', '%Y-%m-%d %H:%i-%s') |
+------------------------------------------------------------+
|                                          1196389819.000000 |
+------------------------------------------------------------+
 
 
---Only matches year, month, and day to display the timestamp
mysql> select unix_timestamp('2007-11-30 10:30%3A19', '%Y-%m-%d');
+-----------------------------------------------------+
| unix_timestamp('2007-11-30 10:30%3A19', '%Y-%m-%d') |
+-----------------------------------------------------+
|                                   1196352000.000000 |
+-----------------------------------------------------+
 
 
---Matching with other characters
mysql> select unix_timestamp('2007-11-30 10:30%3A19', '%Y-%m-%d %H:%i%%3A%s');
+-----------------------------------------------------------------+
| unix_timestamp('2007-11-30 10:30%3A19', '%Y-%m-%d %H:%i%%3A%s') |
+-----------------------------------------------------------------+
|                                               1196389819.000000 |
+-----------------------------------------------------------------+
 
 
---Time beyond the minimum range returns 0
mysql> SELECT UNIX_TIMESTAMP('1970-01-01 00:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('1970-01-01 00:00:00') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
 
 
---Input time with non-zero scale
mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.123');
+-------------------------------------------+
| UNIX_TIMESTAMP('2015-11-13 10:20:19.123') |
+-------------------------------------------+
|                            1447381219.123 |
+-------------------------------------------+
 
---Exceeding the maximum allowed time range
 
mysql> SELECT UNIX_TIMESTAMP('3001-01-19 03:14:07.999999');
+----------------------------------------------+
| UNIX_TIMESTAMP('3001-01-19 03:14:07.999999') |
+----------------------------------------------+
|                                     0.000000 |
+----------------------------------------------+
 
 
---Returns NULL if any argument is NULL
mysql> select unix_timestamp(NULL);
+----------------------+
| unix_timestamp(NULL) |
+----------------------+
|                 NULL |
+----------------------+
 
mysql> select unix_timestamp('2038-01-19 11:14:08',null);
+--------------------------------------------+
| unix_timestamp('2038-01-19 11:14:08',null) |
+--------------------------------------------+
|                                       NULL |
+--------------------------------------------+

keywords

UNIX_TIMESTAMP,UNIX,TIMESTAMP

TO_MONDAY UTC_TIMESTAMP