Wednesday, April 15, 2009

Tip: MySQL and Timestamps as Integers

So, say you managed a legacy project, and on that project, they chose to store timestamps for creation dates in the database. This is quite normal. Now pretend that the timestamp was stored as a TIMESTAMP. Still quite simple. You want to get something after a certain date, just add WHERE CREATED_ON >= '2009-02-01' to get everything February and after. Now imagine that somebody who wrote this application chose instead to store this data as an INTEGER.

This becomes a little more annoying, but not undoable - I had to do a bit of digging in the mysql manual, but here's the query:
select id, first_name, last_name, created_on, as create_date from members where FROM_UNIXTIME( created_on ) >= '2009-02-01';
Hope this helps others out there who suffer from badly designed database maintenance syndrome.
blog comments powered by Disqus