Convert a UNIX timestamp to a datetime with MySQL

Posted January 13th, 2010 in MySql

Dates and times can be stored as an integer value as a UNIX timestamp. This isn't a particularly human readable format but can be converted in MySQL to a datetime value using the FROM_UNIXTIME function.

Dates and times as timestamps

Back when I first started using MySQL and PHP and created an ecommerce system that was used on a number of websites, I decided to store datetimes as a UNIX timestamp for whatever reason. This made it easy to use the PHP date function to format the dates but is not so useful when browsing data in the database or selecting date ranges in a query. I no longer do this and always store date times as a datetime field type now.

Converting integer timestamp to a datetime

In my old ecommerce system there was a table called "orders" with a column called "date_ordered" which was a UNIX timestamp stored as an integer. To convert this to a datetime as part of the SELECT query this needs to be done:

SELECT other fields here, FROM_UNIXTIME(date_ordered)
FROM orders
WHERE ...

The integer field will now be represented in the resultset as a datetime string in the format YYYY-MM-DD HH:MM:SS.