Showing a Column as a UNIX Timestamp in MySQL

1 minute read

I often need to store a timestamp in the UVFood database. There are lots of things I need timestamps for - recording the time that a user account was made or a modification was made, for instance.

While MySQL has quite an assortment of date and time formats which it supports, I usually find it most convenient to store timestamps as an INT and just put the UNIX seconds since the epoch value in there. I don’t need to do funky searches on the timestamps, I just need to do simple comparisons.

A drawback is that sometimes when I’m working with the database by hand it’s annoying to see the timestamps as big numbers - sometimes it would be very helpful to see them as dates and times.

Fortunately there’s a MySQL function that helps with this problem. The “FROM_UNIXTIME()” function will interpret a numeric column’s value as a UNIX seconds-from-the-epoch value and show it as a date and timestamp.

Using it I can easily do things like:

SELECT URL, FROM_UNIXTIME(Timestamp) FROM AccessLog;

and see it as:

+---------------------------+--------------------------+ | URL | FROM_UNIXTIME(timestamp) | +---------------------------+--------------------------+ | explore/users/94 | 2008-10-05 22:49:39 | +---------------------------+--------------------------+

rather than

+---------------------------+------------+ | URL | timestamp | +---------------------------+------------+ | explore/users/94 | 1223261379 | +---------------------------+------------+

Updated: