Generating dynamic dates for MySQL test data

A quickie MySQL tip:

I’ve been working on creating a standard testbed of data (more on that someday) for one of our apps, and the issue of dates came up.  Dates can be a problem in test data if they’re being compared to today’s date, i.e. if it matters if the date field represents something in the past, present or future.

No problem, I thought, instead of setting something up with a string literal, like

date_field = "2008-03-04 12:00:00"

(Assume that’s inside an insert or update command…)

I’d build the string based on the current date, so a field would always be, say, 5 days in the future (assuming the testbed is repopulated daily through an automated job):

date_field = date(date_add(now(), interval 5 day)) + " 12:00:00"

That, as it turns out, doesn’t work so well.  (Oh, another quick tip, check your data after setting up a test suite before going nuts over where your code might be failing).  A quick, easy, and “should have done this in the first place” check reveals:

+-----------------------------------------------------+
| date(date_add(now(), interval 5 day)) + " 12:00:00" |
+-----------------------------------------------------+
|                                                2020 |
+-----------------------------------------------------+

I ended up going with something like this:

date_field = date_format(date_add(now(), interval 5 day), "%Y-%m-%d 12:00:00")

Much better…