Hi guys!
I’m trying to grab all rows whose “date of stay” is within a 72 hour period, and so far I have:
(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?
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?
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…
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?
thread moved back
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.
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?
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.
“within 72 hours” may be clear to you, but it can be interpreted in several ways
or
or
thanks for helping, guido
i hope between the two of us we have made the issue a bit more clear
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.
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!!
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
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).
well, thanks for spelling it out elsewhere, but deciding to withhold this information from us
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.
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.