SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Get rows whose date is within 72 hours?

    Hi guys!

    I'm trying to grab all rows whose "date of stay" is within a 72 hour period, and so far I have:

    Code:
    (TIMESTAMPDIFF(HOUR, bookings_attendees.stay_date, NOW()) < 72)
    But that doesn't appear to be working.

    The field stay_date is of type timestamp, just to head off the obvious question.

    Any ideas?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    since you posted this in the Databases forum, and ~not~ in the MySQL forum, may i enquire as to which database system you're using?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, I did start off in the MySQL forum, but then the website wigged out on me.

    Feel free to move the thread, if need be...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, it's mysql

    next question: within 72 hours in the past, leading up to the current time, or starting with the current time, within 72 hours in the future?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Forbes View Post
    Hmm, I did start off in the MySQL forum, but then the website wigged out on me.

    Feel free to move the thread, if need be...
    thread moved back
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  6. #6
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By using Cron tab, a class method is called which runs a query to find all records whose stay date is within 72 of the moment in time the scheduled class method is called.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    ahem... maybe i didn't ask the question the right way

    do you want stay dates between 2011-07-10 19:45 and 2011-07-13 19:45

    or do you want stay dates between 2011-07-13 19:45 and 2011-07-16 19:45

    or are you maybe even looking for stay dates between 2011-07-10 19:45 and 2011-07-16 19:45

    see the difference?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I honestly can't simplify what I've written, other than re-stating.

    I'm not looking for records between one stay date and another; I'm looking for all records whose stay date is within 72 hours of the moment in time the scheduled class method is called, thus the use of the now() function.

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    "within 72 hours" may be clear to you, but it can be interpreted in several ways

    1) between now() - 72 hours and now() ?

    or

    2) between now() and now() + 72 hours ?

    or

    3) between now() - 72 hours and now() + 72 hours ?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    thanks for helping, guido

    i hope between the two of us we have made the issue a bit more clear
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guys, I have absolutely no idea how you could even begin to interpret what I've written that way.

    Seriously, I've forwarded this onto friends and none of us can understand why you can't understand this.

    I think it's best I go find another forum, because this is getting surreal.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    oh, that's wonderful

    we ask for clarification and you lambaste us instead of answering the question

    how polite

    the good news is, if you should ever decide to come back to sitepoint forums, we will still be here

    and we might ( ~might~ ) answer your questions if/when you do come back

    bye!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    ok so you want all the records that hare within 72 hours from the moment the class is called.....?

    if not then I guess we should leave it there and move on. Forbes, you are always welcome on SPF and we will be here when the surrealism moves through
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  14. #14
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Forbes,

    You want to SELECT the records of whose stay dates are 72 hours or less than (NOW() + 72), correct?

    For example,
    stay_date record 1 is 2011-07-16 17:10:00
    stay_date record 2 is 2011-07-16 16:25:00
    stay_date record 3 is 2011-07-15 11:00:00
    stay_date record 4 is 2011-07-14 12:30:00
    stay_date record 5 is 2011-07-13 10:00:00

    So, say you have your cron/query set to run at 16:30 everyday. On this particular day, 2011-07-13 16:30:00 it runs. The following records will be retrieved.

    stay_date record 2 is 2011-07-16 16:25:00
    stay_date record 3 is 2011-07-15 11:00:00
    stay_date record 4 is 2011-07-14 12:30:00
    stay_date record 5 is 2011-07-13 10:00:00

    Is that what you want? If so, you will need to include more with your condition so that it does not return results further back than the NOW() / query run time (like record 5 in the example).

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by some other forum
    So, if you went to your calendar and looked at todays date, you'd be looking for all events within the next three days.
    well, thanks for spelling it out elsewhere, but deciding to withhold this information from us

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,028
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    WHERE
        bookings_attendees.stay_date BETWEEN
            CURRENT_TIMESTAMP
        AND
            DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)

    That should get you any within the past three days.

    Code SQL:
    WHERE
        bookings_attendees.stay_date BETWEEN
            CURRENT_TIMESTAMP
        AND
            DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 3 DAY)

    That should get you any with the next three days.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Bookmarks

Posting Permissions

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