UNIX_TIMESTAMP() Examples – MySQL

Posted on by Ian

In MySQL, you can use the UNIX_TIMESTAMP() function to return a Unix timestamp. A Unix timestamp is the number of seconds that have elapsed since ‘1970-01-01 00:00:00’ UTC.

You can use this function to return a Unix timestamp based on the current date/time or another specified date/time.

Syntax

You can use any of the following forms:

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)

The (optional) date argument allows you to specify a date for which to calculate the Unix timestamp. If provided, the function returns the value of the argument as seconds since ‘1970-01-01 00:00:00’ UTC.

The date argument can be a date, datetime, or timestamp string, or a number in YYMMDD, YYMMDDHHMMSS, YYYYMMDD, or YYYYMMDDHHMMSS format.

The return value is an integer if no argument is given or the argument does not include a fractional seconds part, or decimal if an argument is given that includes a fractional seconds part.

Example 1 – Using the Current Date/Time

This example uses the current date and time to produce the Unix timestamp.

SELECT UNIX_TIMESTAMP();

Result:

+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1530054626 |
+------------------+

So that is how many seconds have passed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, and the time I ran that query.

Example 2 – Specify a Date

In this example, I provide a date for which to calculate the Unix timestamp from.

SELECT UNIX_TIMESTAMP('1970-01-02') As Result;

Result:

+--------+
| Result |
+--------+
|  50400 |
+--------+

Example 3 – Specify a Datetime Value

In this example, I provide a datetime value.

SELECT UNIX_TIMESTAMP('2021-11-27 12:35:03') AS Result;

Result:

+------------+
| Result     |
+------------+
| 1637980503 |
+------------+

Example 4 – Fractional Seconds

As mentioned, if you provide a fractional seconds part, the return value will be a decimal value (as opposed to integer for the previous examples).

Here’s an example.

SELECT UNIX_TIMESTAMP('2021-11-27 12:35:03.123456') AS Result;

Result:

+-------------------+
| Result            |
+-------------------+
| 1637980503.123456 |
+-------------------+