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()
.
Comments (0)