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