[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.

Timestamp with MySQL

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:

timestamp_column	date_value	date_value
1347811456	2012-09-16 18:04:16	2012-09-16
1347811458	2012-09-16 18:04:18	2012-09-16
1347811462	2012-09-16 18:04:22	2012-09-16
1347811463	2012-09-16 18:04:13	2012-09-16
1347811465	2012-09-16 18:04:15	2012-09-16

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.