SQL Tutorial SQL Advanced SQL Database SQL References

MySQL UNIX_TIMESTAMP() Function



The MySQL UNIX_TIMESTAMP() function returns a Unix timestamp representing seconds since '1970-01-01 00:00:00' UTC. If datetime argument is not provided, the function returns the current Unix timestamp. If the datetime argument is provided, it returns the Unix timestamp for the specified datetime.

The datetime argument can be a DATE, DATETIME, TIMESTAMP string, or a number in YYMMDD, YYMMDDhhmmss, YYYYMMDD, or YYYYMMDDhhmmss format.

Syntax

UNIX_TIMESTAMP(datetime)

Parameters

datetime Optional. Specify a DATE, DATETIME, TIMESTAMP string, or a number in YYMMDD, YYMMDDhhmmss, YYYYMMDD, or YYYYMMDDhhmmss format. If not provided, current timestamp is used.

Return Value

Returns the Unix timestamp.

Example 1:

The example below shows the usage of UNIX_TIMESTAMP() function.

mysql> SELECT UNIX_TIMESTAMP();
Result: 1641883574

mysql> SELECT UNIX_TIMESTAMP('2019-10-25');
Result: 1571961600

mysql> SELECT UNIX_TIMESTAMP('2019-10-25 08:10:25');
Result: 1571991025

mysql> SELECT UNIX_TIMESTAMP(20191025);
Result: 1571961600

mysql> SELECT UNIX_TIMESTAMP(20191025081025);
Result: 1571991025

mysql> SELECT UNIX_TIMESTAMP(NULL);
Result: NULL

Example 2:

Consider a database table called Sample with the following records:

DataLoginStamp
Data 12019-10-25 09:20:38
Data 22019-10-25 09:21:05
Data 32019-10-25 09:24:35
Data 42019-10-25 09:25:24
Data 52019-10-25 09:27:16

The statement given below can be used to convert the records of column LoginStamp into Unix timestamp.

SELECT *, UNIX_TIMESTAMP(LoginStamp) AS UNIX_TIMESTAMP_Value FROM Sample;

This will produce the result as shown below:

DataLoginStampUNIX_TIMESTAMP_Value
Data 12019-10-25 09:20:381571995238
Data 22019-10-25 09:21:051571995265
Data 32019-10-25 09:24:351571995475
Data 42019-10-25 09:25:241571995524
Data 52019-10-25 09:27:161571995636

❮ MySQL Functions