MySQL UNIX_TIMESTAMP() Function

Summary: in this tutorial, you will learn how to use the MySQL UNIX_TIMESTAMP() function to convert a datetime value to a Unix timestamp.

Introduction to MySQL UNIX_TIMESTAMP() function

A Unix timestamp presents the number of seconds that have passed since '1970-01-01 00:00:00' UTC.

The UNIX_TIMESTAMP() function converts a date to a Unix timestamp. Here’s the syntax of the UNIX_TIMESTAMP() function:

UNIX_TIMESTAMP([date])Code language: SQL (Structured Query Language) (sql)

In this syntax:

date: This optional parameter specifies a date value that you want to convert into a Unix timestamp. If you omit it, the function returns the current Unix timestamp.

The UNIX_TIMESTAMP() function returns an integer that represents the Unix timestamp of the specified date.

If the date contains a fractional part of the seconds, the UNIX_TIMESTAMP() returns a decimal number that represents the Unix timestamp.

If the date is NULL, the UNIX_TIMESTAMP() function returns NULL.

Notice that if you pass an out-of-range date to the function, it’ll return 0.

MySQL UNIX_TIMESTAMP() function examples

Let’s take some examples of using the UNIX_TIMESTAMP() function.

1) Basic UNIX_TIMESTAMP() function examples

The following example uses the UNIX_TIMESTAMP() function to convert a date to a Unix timestamp:

SELECT UNIX_TIMESTAMP('2023-10-19 10:45:30');Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------+
| UNIX_TIMESTAMP('2023-10-19 10:45:30') |
+---------------------------------------+
|                            1697687130 |
+---------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

If the datetime value contains a fractional part of seconds, the UNIX_TIMESTAMP() returns a decimal instead. For example:

SELECT UNIX_TIMESTAMP('2023-10-19 10:45:30.9999');Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------------------------------+
| UNIX_TIMESTAMP('2023-10-19 10:45:30.9999') |
+--------------------------------------------+
|                            1697687130.9999 |
+--------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

If you omit the date value, the function returns the current Unix timestamp:

SELECT UNIX_TIMESTAMP();Code language: SQL (Structured Query Language) (sql)

Output:

+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1697686981 |
+------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The following example returns 0 because the date is invalid:

SELECT UNIX_TIMESTAMP('2023-99-99 10:45:30');Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------+
| UNIX_TIMESTAMP('2023-99-99 10:45:30') |
+---------------------------------------+
|                              0.000000 |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using the UNIX_TIMESTAMP() function with table example

First, create a new table that stores the event data with Unix timestamps:

CREATE TABLE events(
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(50) NOT NULL,
    event_time INT NOT NULL
);Code language: SQL (Structured Query Language) (sql)

The events table has three columns: id, event_name, and event_time. The data type of the event_time is an integer that stores the Unix timestamps.

Second, insert some rows into the events table:

INSERT INTO events(event_name, event_time ) 
VALUES
    ('MySQL Enterprise Workshop', UNIX_TIMESTAMP('2021-10-19 15:00:00')), 
    ('MySQL HeatWave and Database Day', UNIX_TIMESTAMP('2021-12-31 23:00:00')), 
    ('MySQL HeatWave Hands-on Lab', UNIX_TIMESTAMP('2022-06-01 12:20:00'));Code language: SQL (Structured Query Language) (sql)

The statement uses the UNIX_TIMESTAMP() function to convert date values into Unix timestamps.

Third, query data from the events table and format the values in the event_time column using the FROM_UNIXTIME() function:

SELECT 
  event_name, 
  FROM_UNIXTIME(event_time) AS event_time 
FROM 
  events;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------+---------------------+
| event_name                      | event_time          |
+---------------------------------+---------------------+
| MySQL Enterprise Workshop       | 2021-10-19 15:00:00 |
| MySQL HeatWave and Database Day | 2021-12-31 23:00:00 |
| MySQL HeatWave Hands-on Lab     | 2022-06-01 12:20:00 |
+---------------------------------+---------------------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Use the MySQL UNIX_TIMESTAMP() function to convert a datetime value to a Unix timestamp.

Was this tutorial helpful?