Showing a Column as a UNIX Timestamp in MySQL
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 |
+---------------------------+------------+
