MySQL UNIX_TIMESTAMP() function

UNIX_TIMESTAMP() function

MySQL UNIX_TIMESTAMP() returns a Unix timestamp in seconds since '1970-01-01 00:00:00' UTC as an unsigned integer if no arguments are passed with UNIX_TIMESTAMP().

When this function used with a date argument, it returns the value of the argument as an unsigned integer in seconds since '1970-01-01 00:00:00' UTC.

The argument may be a DATE, DATETIME,TIMESTAMP or a number in YYYYMMDD or YYMMDD.

Note: Since UNIX_TIMESTAMP() works on current datetime, your output may vary from the output shown.

This function is useful in -

• The Unix timestamp is a standardized way to represent time across different systems and platforms.
• The Unix timestamp is an absolute measure of time, which allows for precise date and time comparisons.
• UNIX_TIMESTAMP() is valuable for filtering records or data based on time criteria, such as selecting records within a specific time range.
• The Unix timestamp is commonly used in time series analysis to represent and analyze data over time.
• It's crucial in real-time applications where precise time measurements are required, such as in financial trading platforms.
• UNIX_TIMESTAMP() is used in log tables to record when events or actions occurred, providing an audit trail for system activity.
• The Unix timestamp is a standard format for exchanging time-related data between different systems or applications.

Syntax:

UNIX_TIMESTAMP(); UNIX_TIMESTAMP(date);

Arguments:

Syntax Diagram: 1

Syntax Diagram: 2

MySQL Version: 8.0

Pictorial Presentation:

Example:

The following statement will return the unix timestamp in seconds as an unsigned integer since '1970-01-01 00:00:00' UTC.

Code:

SELECT UNIX_TIMESTAMP();

Output:

mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1429048385 | 
+------------------+
1 row in set (0.01 sec)

Example : UNIX_TIMESTAMP() on a specified date

The following statement will return the unix timestamp in seconds as an unsigned integer since '1970-01-01 00:00:00' UTC for the specified datetime 1970-01-01 12:00:00.

Code:

SELECT UNIX_TIMESTAMP('1970-01-01 12:00:00');

Output:

mysql> SELECT UNIX_TIMESTAMP('1970-01-01 12:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('1970-01-01 12:00:00') |
+---------------------------------------+
|                                 72000 | 
+---------------------------------------+
1 row in set (0.00 sec)

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: TO_DAYS()
Next: UTC_DATE()