Converting Timestamps to Dates in MySQL

MySQL provides several functions for working with dates and timestamps. In this tutorial, we’ll cover how to convert a timestamp to a date in MySQL.

A timestamp is a numeric representation of a point in time, typically represented as the number of seconds that have elapsed since January 1, 1970, at 00:00:00 UTC. To convert a timestamp to a date, you can use one of several functions provided by MySQL.

Using FROM_UNIXTIME

The FROM_UNIXTIME function converts a Unix timestamp (a numeric value representing the number of seconds that have elapsed since January 1, 1970) to a datetime string. You can use this function to convert a timestamp to a date.

SELECT FROM_UNIXTIME(1643723400);

This will return a datetime string in the format YYYY-MM-DD HH:MM:SS.

To specify a different format for the output, you can pass a second argument to the FROM_UNIXTIME function. For example:

SELECT FROM_UNIXTIME(1643723400, '%Y-%m-%d');

This will return a string in the format YYYY-MM-DD.

Using DATE_FORMAT

The DATE_FORMAT function formats a date or datetime value as a string. You can use this function to convert a timestamp to a date.

SELECT DATE_FORMAT(FROM_UNIXTIME(1643723400), '%Y-%m-%d');

This will return a string in the format YYYY-MM-DD.

Using CAST

You can also cast a timestamp to a date using the CAST function. For example:

SELECT CAST(1643723400 AS DATE);

However, this method only works if the timestamp is stored as a numeric value representing the number of seconds that have elapsed since January 1, 1970.

Using DATE

If the timestamp is stored in a column with a data type of TIMESTAMP, you can use the DATE function to convert it to a date. For example:

SELECT DATE(timestamp_column) FROM table_name;

This will return the date part of the timestamp, without the time component.

Example Use Case

Suppose we have a table called users with a column called registration_timestamp that stores the timestamp when each user registered. We can use the FROM_UNIXTIME function to convert this timestamp to a date and format it as a string:

SELECT 
  email,
  name,
  FROM_UNIXTIME(registration_timestamp, '%Y-%m-%d') AS registration_date
FROM users;

This will return a result set with the user’s email, name, and registration date in the format YYYY-MM-DD.

In summary, converting a timestamp to a date in MySQL can be done using one of several functions: FROM_UNIXTIME, DATE_FORMAT, CAST, or DATE. The choice of function depends on the data type of the timestamp column and the desired output format.