Select rows with dates in upcoming days

Cloud Ghost · 06-20-2012, 03:43 AM

Hey,

I setup a database to store users' birthdays in MySQL's datetime format. I have been able to show birthdays on a certain day and month with MySQL's DAY() and MONTH() functions but I now want to show birthdays in the next 7 days. I have tried doing that with DATE_ADD() and INTERVAL and stuff but it didn't work. I believe those functions take the year into account when the query is executed but I don't want that.

Help please?

guelphdad · 06-20-2012, 03:55 AM

please show us your work. we can guide you from there.

Cloud Ghost · 06-20-2012, 04:13 AM

Alright. I didn't think you'd need it because there's not much to the query and I already said I'm using the MONTH() and DAY() functions right now but here's the query:

Code:

SELECT f.entity_id, f.field_birthday_value FROM field_data_field_birthday AS f WHERE MONTH(f.field_birthday_value) = '$month' AND DAY(f.field_birthday_value) = '$day'

Well actually, that's the query that selects the birthdays on a certain day but I'd expect it will just be modified to select birthdays in the next 7 days.

Old Pedant · 06-20-2012, 04:52 AM

It's not trivial. You have to worry about wrapping around to the next year.

Easiest way to do this in MySQL is to use the DAYOFYEAR() function to transform the person's birthdate into a date in the current year:

Code:

WHERE DATEADD( DATESUB( CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) DAY ), INTERVAL DAYOFYEAR(birthdate) DAY )
BETWEEN CURDATE() AND DATEADD( CURDATE(), INTERVAL 7 DAY )

You see it?

Start with CURDATE(). Subtract from that the number of days in the year it represents. That gives you December 31st of prior year. To that, you add the number of days the birthdate represents.

Then you check that to see if that result is between today and 7 days from now.

Example: Let's pretend today is Feb 4th, 2013. And person's birthday is Jan 28, 1970.

Okay, in parts:

Code:

    DATESUB( CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) DAY )
becomes
    DATESUB( '2013-2-4', INTERVAL DAYOFYEAR('2013-2-4' DAY )
which is
    DATESUB( '2013-2-4', INTERVAL 35 DAY )
which is
    '2012-12-31'

So then
    DATEADD( DATESUB( CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) DAY ), INTERVAL DAYOFYEAR(birthdate) DAY )
becomes 
    DATEADD( '2012-12-31', INTERVAL DAYOFYEAR(birthdate) DAY )
which becomes
    DATEADD( '2012-12-31', INTERVAL DAYOFYEAR('2011-1-27') DAY )
which becomes
    DATEADD( '2012-12-31', INTERVAL 27 DAY )
which becomes
   '2013-1-27'

And we finally do
   WHERE '2013-1-27' BETWEEN CURDATE() AND DATEADD( CURDATE(), INTERVAL 7 DAY ) 
which becomes
   WHERE '2013-1-27' BETWEEN '2013-2-4' AND DATEADD( '2013-2-4', INTERVAL 7 DAY ) 
which is finally
   WHERE '2013-1-27' BETWEEN '2013-2-4' AND '2013-2-11'

Which means it will be false.

But if the birth date had been '1970-2-8' (for example) you can surely see how it WOULD be between those two dates and so the WHERE would be true.

** UNTESTED ** but I think it should work universally, even in leap years and around Feb 29th.

Old Pedant · 06-20-2012, 04:58 AM

Whoops...I lied.

If the birthday is, say, January 3rd and today is December 30th, KABLOOEY.

Because of course we would convert '1970-1-3' to (say) '2012-1-3' and of course that is not between '2012-12-30' and '2013-1-6'.

My head hurts.

Can we just OR the expression? Between the two dates as I gave it and then also between two dates one year earlier??? I think that would work.

Cloud Ghost · 06-20-2012, 06:45 AM

Wow thanks! I had trouble getting it to work at first so I changed DATEADD() and DATESUB() to DATE_ADD() and DATE_SUB(). We must have different MySQL versions or something.

Anyway, my head hurts as well, lol. If it's valid to have b OR c BETWEEN a AND e (which dates instead) then I guess I could just have DATE_SUB( CURDATE(), INTERVAL -1 YEAR ). Would that work??

Old Pedant · 06-20-2012, 07:20 PM

You mean (b or c) between a and e ?? Two choices? No, sorry, not legal.

I haven't given up on this. Funny thing is, this one is easy to do with ACCESS! Yes, brain-dead littler Access. Because it has an easy way of building up a date from year/month/day.

Cloud Ghost · 06-20-2012, 11:49 PM

Alright then I guess I'll have to do two BETWEENs and repeat the code.

How would I get the birthday one year back? Would I have to put the first DATE_ADD() in another DATE_ADD() with a -1 YEAR interval?

I have only used Access for simple things in the past so I wouldn't know.

Old Pedant · 06-21-2012, 12:50 AM

Quote:

Originally Posted by Cloud Ghost

How would I get the birthday one year back? Would I have to put the first DATE_ADD() in another DATE_ADD() with a -1 YEAR interval?

That would work. But I'm still thinking on this, to see if there's an easier way.

I may have it.

How about this:

Code:

WHERE DAYOFYEAR(birthdate) BETWEEN DAYOFYEAR(CURDATE()) AND DAYOFYEAR(CURDATE()) + 7
   OR DAYOFYEAR(birthdate)+365 BETWEEN DAYOFYEAR(CURDATE()) AND DAYOFYEAR(CURDATE()) + 7

??

Let's take some scenarios:

birthdate, Jan 24. Today Jan 20.

So that query becomes (effectively):

Code:

WHERE 24 BETWEEN 20 AND 27
   OR 389 BETWEEN 20 AND 27

Indded, birthdate is within next 7 days, and we get TRUE.

birthdate December 29, Today December 27.
So that query becomes (effectively):

Code:

WHERE 363 BETWEEN 361 AND 368
   OR 728 BETWEEN 361 AND 368

Still okay.

birtdate Jan 2, Today December 29.
So that query becomes (effectively):

Code:

WHERE 2 BETWEEN 363 AND 370
   OR 367 BETWEEN 363 AND 370

And still okay.

I think that works. No?

The +365 maybe could be adjusted to be +366 if current year is a leap year, of course. E.g., Dec 29 of 2012 has DAYOFYEAR 364, not 363. You could more easily check which to use in the PHP/ASP/JSP code than in the MySQL code, since it's the same for all records in the query.

Cloud Ghost · 06-21-2012, 02:24 AM

That works. Thanks a lot for your help, Old Pedant.

And yeah, I used PHP to check whether I should use 365 or 366.