Showing a Column as a UNIX Timestamp in MySQL

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

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 |
+---------------------------+------------+