Guys, I'm having a serious brain fart. It's a very simple question and I can't seem to get the simple answer to go along with it...
K, Given the following schema:
Table Name: date_orders
Fields: ( Start_date, Start_time, End_Date, End_Time)
I need to say "Given this time and date, give me all the upcoming records"
Take for instance a record set like this:
Id = 1
Start_Date = 10-29-2008
Start_Hour = 22:00:00
End_Date = 11-14-2008
End_Hour = 21:00:00
Id = 2
Start_Date = 10-29-2008
Start_Hour = 20:00:00
End_Date = 11-14-2008
End_Hour = 20:30:00
Id = 3
Start_Date = 10-29-2008
Start_Hour = 20:00:00
End_Date = 10-29-2008
End_Hour = 20:30:00
Id = 4
Start_Date = 10-29-2008
Start_Hour = 11:00:00
End_Date = 11-14-2008
End_Hour = 14:00:00
-----------------------------
Out of this data, I need it to say, ok, the upcoming records are 1,2,4
Since 1,2,4 have date ranges that go beyond today's date and will eventually fire off at the same time tomorrow..
Any help would be greatly appreciated.
[SOLVED]Having a brain fart - need some help with a DB query
Moderator: General Moderators
[SOLVED]Having a brain fart - need some help with a DB query
Last edited by infolock on Wed Oct 29, 2008 7:46 pm, edited 1 time in total.
Re: Having a brain fart - need some help with a DB query..
I'm assuming that your dates and times are all datetime data types.
So what are your criteria for "upcoming records"? That could mean several different things. Try expressing it like this:
"I want to extract those records where the End-Hour on the End-Date is later than right now."
That seems to be what you are asking for. Are you sure that the Start_Date and Start-Hour are not part of your definition?
Assuming the above definition, your query could be:
So what are your criteria for "upcoming records"? That could mean several different things. Try expressing it like this:
"I want to extract those records where the End-Hour on the End-Date is later than right now."
That seems to be what you are asking for. Are you sure that the Start_Date and Start-Hour are not part of your definition?
Assuming the above definition, your query could be:
Code: Select all
SELECT * FROM date_orders WHERE Date('Y-m-d') < End_Date
OR (Date('Y-m-d') = End_Date AND Date('H:i:s') <= End_Hour)Re: Having a brain fart - need some help with a DB query..
Thanks for the help but I already figured out my own solution haha..
I was thinking about doing Datetime fields, but it's a little bit easier to just seperate them up...
Anyways, solution is as follows
<code>
# SELECT id FROM datel_order
# WHERE (date(start_date) >= '$date' AND date(end_date) >= '$date')
# AND (time(start_time) <> '$time' AND time(end_time) <> '$time')
# AND (time('$time') NOT BETWEEN start_time AND end_time) ORDER BY start_time ASC
#
</code>
I was thinking about doing Datetime fields, but it's a little bit easier to just seperate them up...
Anyways, solution is as follows
<code>
# SELECT id FROM datel_order
# WHERE (date(start_date) >= '$date' AND date(end_date) >= '$date')
# AND (time(start_time) <> '$time' AND time(end_time) <> '$time')
# AND (time('$time') NOT BETWEEN start_time AND end_time) ORDER BY start_time ASC
#
</code>