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