How to convert timestamp to datetime in MySQL?

In MySQL, you can convert timestamps to date and time format using the built-in function FROM_UNIXTIME. This function converts Unix timestamps (measured in seconds) to a human-readable date and time format. Unix timestamp represents the number of seconds elapsed since January 1, 1970 (UTC).

Using the FROM_UNIXTIME Function

Basic syntax is as follows:

sql
FROM_UNIXTIME(unix_timestamp, format)

• unix_timestamp is the Unix timestamp to be converted.
• format is an optional parameter that defines the output date and time format. If not specified, MySQL uses the default format YYYY-MM-DD HH:MM:SS.

Example:

Suppose you have a column created_at containing Unix timestamps, and you want to convert it to a human-readable date and time format.

1. Convert to Default Format:

sql
SELECT FROM_UNIXTIME(created_at) AS formatted_date FROM your_table_name;

This will output something like 2021-03-15 12:45:34.

1. Specify Custom Format:

If you want to output a different date and time format, such as only the year, month, and day:

sql
SELECT FROM_UNIXTIME(created_at, '%Y-%m-%d') AS formatted_date FROM your_table_name;

This will output something like 2021-03-15.

Practical Application Example:

Suppose you work on an e-commerce website and need to analyze daily user registrations. The user registration time is stored in the database as a Unix timestamp. You can use the FROM_UNIXTIME function to convert these timestamps to date format, group and count by date to obtain the number of registrations per day:

sql
SELECT 
    FROM_UNIXTIME(created_at, '%Y-%m-%d') AS registration_date,
    COUNT(*) AS total_registrations
FROM 
    users
GROUP BY 
    registration_date;

This query will help you obtain the daily registration count, facilitating further data analysis and reporting.