[MySQL] Transform a timestamp into a date or datetime

To save a date in a database, it is possible to store the data of the UNIX timestamp (example: 1348254909) or use the format dated (example: 2012-12-31) or datetime (example: 2012-12-31 23:59:59). This article explains how to turn a timestamp into a date or datetime.

Request to transform the timestamp

In MySQL you must use the FROM_UNIXTIME () function to transform a value of timestamp in datetime. From this function, just use the CAST () cast function to transform a datetime in dated. Therefore, here is an example of a request that allows you to collect a date in the desired format:

SELECT colonne_timestamp, 
  FROM_UNIXTIME(colonne_timestamp) as valeur_datetime,
  CAST(FROM_UNIXTIME(colonne_timestamp) as date) as valeur_date
FROM table

This query returns the following results:

Save the timestamp as a datetime

Displaying the result in a SELECT is practical, but if the goal is to change the way dates are stored while keeping the history, you must use an UPDATE as follows:

UPDATE table
SET colonne_datetime = FROM_UNIXTIME(colonne_timestamp)

Interest

Changing the value of timestamp to datetime has a few advantages:

• Easier to read for a human
• Easier to select data based on year, month, day, hour …
• On January 19, 2038 the UNIX timestamp will no longer work, but the datetime will still work

If you want to check the date and time of a timestamp it is possible to use an online tool to convert the timestamp.