MySQL default values should be better

I was creating a table a little while ago that stores a timestamp that is essentially an expiry date. I had wanted this to be a date 2 weeks in advance of the current date and time. With a timestamp field, however, the only non-constant default value available is CURRENT_TIMESTAMP. This, obviously, represents the moment in time that the row is created. I wanted to be able to do something more like the following:

expiry_date TIMESTAMP NOT NULL DEFAULT DATE_ADD(NOW(), INTERVAL 14 DAY)

Unfortunately that’s not allowed at all. Perhaps at some point in the future it will be though, and I look forward to that day.

If you’re doing this yourself and your expiry date is absolutely always going to be the same amount of time into the future then you can use something like the following:

start_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

You could then use something like the following query to fetch all rows where the expiry date has not been reached:

SELECT * FROM table_name WHERE DATE_ADD(start_date, INTERVAL 14 DAY) < NOW();

Oh well, just another little MySQL thing I found a bit interesting while I was poking around. 🙂