Introduction:

Data often comes in various formats, and one common challenge is converting between these formats. One such scenario involves converting an integer (INT) representation of a date or timestamp into the more human-readable DATETIME format in MySQL. In this blog, we will explore multiple methods for achieving this conversion.

Method 1: Using the FROM_UNIXTIME() Function

The simplest way to convert an INT timestamp to DATETIME in MySQL is by using the built-in FROM_UNIXTIME() function. This function takes an INT value representing Unix timestamp (seconds since the epoch) and returns a DATETIME representation. Here's how you can use it:

SELECT FROM_UNIXTIME(your_int_column) AS converted_datetime FROM your_table;

Output:

The FROM_UNIXTIME() function seamlessly converts the given INT timestamp to a human-readable DATETIME format. It takes care of the conversion process by parsing the seconds since the Unix epoch and formatting it accordingly.

Method 2: Using DATE_ADD() and DATE_FORMAT() Functions

Another approach involves using the combination of DATE_ADD() and DATE_FORMAT() functions to achieve the conversion. This method allows for more flexibility in handling various date/time components:

SELECT DATE_FORMAT(DATE_ADD('1970-01-01', INTERVAL your_int_column SECOND), '%Y-%m-%d %H:%i:%s') AS converted_datetime
FROM your_table;

Output:

In this method, we first add the INT timestamp as seconds to the base date '1970-01-01' using DATE_ADD(). Then, we format the resulting DATETIME using DATE_FORMAT() to match the desired format.

Method 3: Using CAST() or CONVERT() Functions

The CAST() and CONVERT() functions can also be utilized to convert an INT timestamp to DATETIME:

SELECT CAST(FROM_UNIXTIME(your_int_column) AS DATETIME) AS converted_datetime FROM your_table;

-- or

SELECT CONVERT(FROM_UNIXTIME(your_int_column), DATETIME) AS converted_datetime FROM your_table;

Output:

Both CAST() and CONVERT() functions are used to explicitly specify the desired data type conversion. In this case, we convert the result of FROM_UNIXTIME() to the DATETIME data type.

Method 4: Using STR_TO_DATE() Function

If your INT timestamp follows a specific format, you can use the STR_TO_DATE() function:

SELECT STR_TO_DATE(your_int_column, '%Y%m%d%H%i%s') AS converted_datetime FROM your_table;

Output:

The STR_TO_DATE() function allows you to explicitly define the format of the input string using format specifiers, resulting in accurate and consistent conversions.

Conclusion:

In this blog, we have learned four distinct methods: using the FROM_UNIXTIME() function, employing DATE_ADD() and DATE_FORMAT() functions, utilizing CAST() or CONVERT() functions, and leveraging the STR_TO_DATE() function.