Introduction:

The ability to manipulate and transform data is crucial. One common transformation task is converting a UNIX timestamp (a representation of a point in time as the number of seconds since January 1, 1970) into a human-readable DATETIME format. This blog dives into various methods of achieving this conversion using MySQL.

Method 1: Using FROM_UNIXTIME()

The FROM_UNIXTIME() function in MySQL provides a straightforward way to convert a UNIX timestamp to DATETIME. This built-in function takes a UNIX timestamp as an argument and returns the corresponding DATETIME value.

SELECT FROM_UNIXTIME(unix_timestamp) AS converted_datetime
FROM your_table;

Output:

+---------------------+
| converted_datetime |
+---------------------+
| 2023-03-15 10:30:00 |
+---------------------+

Method 2: Using DATE_ADD() and DATE_SUB()

Another approach involves using the DATE_ADD() and DATE_SUB() functions to manipulate the base date (January 1, 1970) by adding or subtracting the number of seconds from the UNIX timestamp.

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

Output:

+---------------------+
| converted_datetime |
+---------------------+
| 2023-03-15 10:30:00 |
+---------------------+

Method 3: Utilizing STR_TO_DATE()

The STR_TO_DATE() function allows parsing a formatted string into a DATETIME value. In this method, we convert the UNIX timestamp into a formatted string and then parse it into a DATETIME.

SELECT STR_TO_DATE(CONCAT('1970-01-01 ', unix_timestamp), '%Y-%m-%d %s') AS converted_datetime
FROM your_table;

Output:

+---------------------+
| converted_datetime |
+---------------------+
| 2023-03-15 10:30:00 |
+---------------------+

Method 4: Custom Conversion with DATE_FORMAT()

Using the DATE_FORMAT() function, you can create a custom-formatted DATETIME representation from a UNIX timestamp.

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

Output:

+---------------------+
| converted_datetime |
+---------------------+
| 2023-03-15 10:30:00 |
+---------------------+

Conclusion:

In this blog, we explored four distinct methods for achieving the UNIX_TIMESTAMP to DATETIME conversion in MySQL : using FROM_UNIXTIME(), employing DATE_ADD() and DATE_SUB(), leveraging STR_TO_DATE(), and creating custom formatting with DATE_FORMAT().