Introduction:

The UNIX timestamp format (also known as Unix Epoch time) is a widely used representation of time as the number of seconds that have passed since January 1, 1970, at 00:00:00 UTC. While it's a useful format for computation, displaying and interpreting these timestamps in a human-readable format often requires conversion to the DATETIME format. In this blog, we will explore various methods to convert UNIX_TIMESTAMP to DATETIME in MySQL.

Method 1: Using FROM_UNIXTIME() Function

The FROM_UNIXTIME() function in MySQL allows you to convert a UNIX timestamp to a DATETIME value. This method is straightforward and efficient.

SELECT FROM_UNIXTIME(unix_timestamp_column) AS converted_datetime FROM your_table;

Output:

The FROM_UNIXTIME() function takes the UNIX timestamp as its argument and returns a DATETIME value in the default format ('YYYY-MM-DD HH:MI:SS').

Method 2: Using DATE_ADD() Function

You can also use the DATE_ADD() function along with the UNIX timestamp to achieve the conversion.

SELECT DATE_ADD('1970-01-01', INTERVAL unix_timestamp_column SECOND) AS converted_datetime FROM your_table;

Output:

In this method, we use the DATE_ADD() function to add the number of seconds represented by the UNIX timestamp to the base date '1970-01-01', effectively converting it into a DATETIME value.

Method 3: Using STR_TO_DATE() Function

The STR_TO_DATE() function can be utilized to convert UNIX timestamps to DATETIME format.

SELECT STR_TO_DATE(unix_timestamp_column, '%s') AS converted_datetime FROM your_table;

Output:

The STR_TO_DATE() function parses the input string based on the provided format ('%s' in this case, which represents seconds since 1970-01-01 00:00:00 UTC) and returns the corresponding DATETIME value.

Method 4: Using Custom SQL Calculations

You can create a custom SQL expression to convert the UNIX timestamp to DATETIME.

SELECT 
    DATE_FORMAT(FROM_UNIXTIME(unix_timestamp_column), '%Y-%m-%d %H:%i:%s') AS converted_datetime
FROM your_table;

Output:

In this approach, we first use the FROM_UNIXTIME() function to convert the UNIX timestamp to DATETIME, and then the DATE_FORMAT() function to format the resulting DATETIME value as desired.

Conclusion:

In this blog, we have learned various methods to convert UNIX_TIMESTAMP to DATETIME in MySQL. Whether you prefer using built-in functions like FROM_UNIXTIME() or custom SQL expressions, MySQL provides versatile tools to handle timestamp conversions effectively.