Convert Timestamps to Dates in MySQL

In MySQL, you can convert timestamps to dates using the FROM_UNIXTIME() function, which converts a UNIX timestamp into a date format.

In this tutorial, you will learn how to use the FROM_UNIXTIME() function to convert timestamps into human-readable date formats in MySQL.

Converting Timestamps to Dates in MySQL using FROM_UNIXTIME()

Timestamps in MySQL are typically stored as UNIX timestamps, representing the number of seconds since January 1, 1970. To make these timestamps readable, MySQL offers the FROM_UNIXTIME() function, which converts them into a standard date format.

Examples: Converting Timestamps to Dates in MySQL

Here are some examples demonstrating how to use FROM_UNIXTIME() to convert timestamps into dates.

1. Converting a UNIX Timestamp to Date

To convert a timestamp like 1672531199 into a readable date:

</>
Copy
SELECT FROM_UNIXTIME(1672531199) AS converted_date;

This query converts the timestamp 1672531199 to a date format, resulting in something like '2023-01-01 00:00:00', depending on your time zone settings.

Convert Timestamps to Dates in MySQL - Example: Converting a UNIX Timestamp to Date

2. Formatting the Converted Date

To convert a timestamp and format it as YYYY-MM-DD only:

</>
Copy
SELECT DATE(FROM_UNIXTIME(1672531199)) AS formatted_date;

This query converts the timestamp and then extracts only the date portion, resulting in a format like '2023-01-01'.

Convert Timestamps to Dates in MySQL - Example: Formatting the Converted Date

3. Converting Timestamps in a Table Column

If you have a table events with a timestamp column, you can convert each timestamp to a date format:

</>
Copy
SELECT event_name, FROM_UNIXTIME(timestamp) AS event_date
FROM events;

This query retrieves each event’s timestamp and converts it to a readable date, useful for displaying event dates in reports.