gistfile1.sql

-- Looking for records that will expire 3 or 9 months from today.

-- Original query.
--   DATE(NOW()) = DATE_SUB(`Dress`.`expiration_date`, INTERVAL 3 MONTH) OR
--   DATE(NOW()) = DATE_SUB(`Dress`.`expiration_date`, INTERVAL 9 MONTH)

-- Start a select query.
SELECT

-- PROBLEM
-- Looking for 2014-08-31, checking dates around three months ahead will never find a match.
DATE_SUB('2014-11-30', INTERVAL 3 MONTH) as 'Yesterday 1', # 2014-08-30
                                                           # 2014-08-31 is not solveable
DATE_SUB('2014-12-01', INTERVAL 3 MONTH) as 'Tomorrow 1',  # 2014-09-01

-- Replacing DATE_SUB with regular math has same results.
('2014-11-30' - INTERVAL 3 MONTH) as 'Yesterday 2',        # 2014-08-30
                                                           # 2014-08-31 is not solveable
('2014-12-01' - INTERVAL 3 MONTH) as 'Tomorrow 2',         # 2014-09-01

-- Changing the math around to the other side gives duplicate results.
('2014-08-30' + INTERVAL 3 MONTH ) as 'Yesterday + 3m',    # 2014-11-30
('2014-08-31' + INTERVAL 3 MONTH ) as 'Today + 3m',        # 2014-11-30 is same as yesterday
('2014-09-01' + INTERVAL 3 MONTH ) as 'Tomorrow + 3m',     # 2014-12-01

-- SOLUTION
-- Changing to DAY intervals solves the problem. Adjusting up to 92 gets us in sync with the old reminder times.
DATE_SUB('2014-11-30', INTERVAL 92 DAY) as 'Yesterday',    # 2014-08-30
DATE_SUB('2014-12-01', INTERVAL 92 DAY) as 'Today',        # 2014-08-31 yay, finally today
DATE_SUB('2014-12-02', INTERVAL 92 DAY) as 'Tomorrow',     # 2014-09-01

-- It probably makes sense to change this to 91 at the beginning of next month
DATE_SUB('2014-11-30', INTERVAL 91 DAY) as 'Today',        # 2014-08-31 - 11/30 already ran yesterday
DATE_SUB('2014-12-01', INTERVAL 91 DAY) as 'Tomorrow',        # 2014-09-01

-- Let's test a few more dates around Feb. Looks goods
DATE_SUB('2015-05-30', INTERVAL 92 DAY) as 'Feb 28',       # 2015-02-28
DATE_SUB('2015-05-31', INTERVAL 92 DAY) as 'Mar 01',       # 2014-03-01
DATE_SUB('2015-06-01', INTERVAL 92 DAY) as 'Mar 02',       # 2014-03-02

-- Now let's test 9 months out with DAY interval. There were no reminders near this range, so no need for adjustments.
DATE_SUB('2015-05-30', INTERVAL 270 DAY) as 'Yesterday',    # 2014-08-30
DATE_SUB('2015-05-31', INTERVAL 270 DAY) as 'Today',        # 2014-08-31
DATE_SUB('2015-06-01', INTERVAL 270 DAY) as 'Tomorrow'      # 2014-09-01