MySQL Time interval on a query

Question

I want to run a cron job through my website, that will pick up all email addresses that haven't been active from 2 months and 1 day.

I've seen this post:

How to select last 6 months from news table using MySQL

And so I know I want to do something like this:

SELECT DISTINCT email FROM orders WHERE date = DATE_SUB(now(), INTERVAL 2 MONTH)

The additional day is what has me a little stuck - do I have to specify a number of days like this:

SELECT DISTINCT email FROM orders WHERE date = DATE_SUB(now(), INTERVAL 63 DAYS)

But then I'm concerned about months with different lengths in them (29, 30, 31), so is it also a case of having to write a small script to check on the number of days in the month before setting the interval?

Possibly I'm making this too complicated so any help would be appreciated!

Thanks

Answer 1

You can only use one type at a time with INTERVAL. There are several "hybrids" like HOUR_SECOND or YEAR_MONTH but there doesn't seem to be any MONTH_DAY hybrid type available that would be used for an interval of j months and k days.

That being said, the DATE_ADD and DATE_SUB functions exist to make simple date arithmetic more readable. You can still use date arithmetic in its more basic, but less readable form—take a look at what happens when you run the following queries:

CREATE TEMPORARY TABLE foo (SELECT NOW() a, NOW()+1 b, NOW()+10000000000001 c);

SELECT * FROM foo;

DESCRIBE foo;

Don't squint too long at the second query before you try running the third. What's going on here is that MySQL can represent a DATETIME in a few ways, including as a string: 'YYYY-MM-DD hh:mm:ss' or, equivalently, as a double-precision numeric value: YYYYMMDDhhmmss.ffffff where the f represents a decimal fraction of a second to six places (microsecond resolution).

Understanding the above, the naive solution might be:

SELECT NOW() - 00000201000000.000000
             # YYYYMMDDhhmmss.ffffff   (format of above)

However, even though you're representing a datetime, you're still using a number, so you don't need all those extra leading zeros or empty decimal places. You can do this instead:

SELECT NOW() - 201000000
             # MDDhhmmss   (format of above)

Finally, you can CAST this numerical result back to the DATETIME format for use in your query:

SELECT DISTINCT email FROM orders WHERE date = CAST(NOW() - 201000000 AS DATETIME)

There are dozens of other ways to do this. My preference would probably be the following, for readability:

SELECT DISTINCT email FROM orders WHERE date = ((NOW() - INTERVAL 2 MONTH) - INTERVAL 1 DAY)

The grouping in the arithmetic expression is not required, but I'd recommend using at least the outer set of parentheses to emphasize that both subtractions are intended. Personally, if I saw NOW() - INTERVAL 2 MONTH - INTERVAL 1 DAY I might wonder if someone had intended to change from one interval to the other and simply forgotten to delete a bit of code. But the style choice is yours (and the most explicit thing would be to comment your code anyway).