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.