MySQL Time interval on a query

date, intervals, mysql

Solution

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).

Problem

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

Original source