MySQL FROM_UNIXTIME() Function

Summary: in this tutorial, you will learn how to use the MySQL FROM_UNIXTIME() function to convert a UNIX timestamp into a readable date and time format.

Introduction to MySQL FROM_UNIXTIME() function

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

To convert a Unix timestamp into a readable date and time format, you use the FROM_UNIXTIME() function.

Here’s the syntax of the FROM_UNIXTIME() function:

FROM_UNIXTIME(unix_timestamp, [format])Code language: SQL (Structured Query Language) (sql)

In this syntax:

• unix_timestamp: This is a Unix timestamp that you want to convert into a date and time format.
• format: This optional parameter allows you to specify a format for the output. If you omit it, the function will return the default date and time format.

The FROM_UNIXTIME() function returns a datetime of the Unix timestamp using the session time zone.

MySQL FROM_UNIXTIME() function examples

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

1) Basic FROM_UNIXTIME() function example

The following example uses the FROM_UNIXTIME() function to convert the Unix timestamp 1697684935 to a datetime value:

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

Output:

+---------------------------+
| FROM_UNIXTIME(1697684935) |
+---------------------------+
| 2023-10-19 10:08:55       |
+---------------------------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

2) Using FROM_UNIXTIME() function with custom date format

The following example uses the FROM_UNIXTIME() function with a custom date format "YYYY.MM.DD":

SELECT FROM_UNIXTIME(1697684935, '%Y.%m.%d');Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------+
| FROM_UNIXTIME(1697684935, '%Y.%m.%d') |
+---------------------------------------+
| 2023.10.19                            |
+---------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Using FROM_UNIXTIME() function with table data

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

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)

Second, insert some rows into the events table:

INSERT INTO events(event_name, event_time ) 
VALUES
    ('MySQL Enterprise Workshop', 1634630400), 
    ('MySQL HeatWave and Database Day', 1640966400), 
    ('MySQL HeatWave Hands-on Lab', 1654060800);Code language: SQL (Structured Query Language) (sql)

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 FROM_UNIXTIME() function to convert a UNIX timestamp into a readable date and time format.