Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Jan 2010
    Location
    Canada
    Posts
    34
    Thanks
    11
    Thanked 4 Times in 4 Posts

    Select rows with dates in upcoming days

    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?

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    please show us your work. we can guide you from there.

  • #3
    New Coder
    Join Date
    Jan 2010
    Location
    Canada
    Posts
    34
    Thanks
    11
    Thanked 4 Times in 4 Posts
    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.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,031
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Cloud Ghost (06-20-2012)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,031
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New Coder
    Join Date
    Jan 2010
    Location
    Canada
    Posts
    34
    Thanks
    11
    Thanked 4 Times in 4 Posts
    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??

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,031
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New Coder
    Join Date
    Jan 2010
    Location
    Canada
    Posts
    34
    Thanks
    11
    Thanked 4 Times in 4 Posts
    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.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,031
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    Quote Originally Posted by Cloud Ghost View Post
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Cloud Ghost (06-21-2012)

  • #10
    New Coder
    Join Date
    Jan 2010
    Location
    Canada
    Posts
    34
    Thanks
    11
    Thanked 4 Times in 4 Posts
    That works. Thanks a lot for your help, Old Pedant.

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •