Page 1 of 1

[SOLVED]Having a brain fart - need some help with a DB query

Posted: Wed Oct 29, 2008 7:05 pm
by infolock
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.

Re: Having a brain fart - need some help with a DB query..

Posted: Wed Oct 29, 2008 7:24 pm
by califdon
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:

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..

Posted: Wed Oct 29, 2008 7:44 pm
by infolock
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>