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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

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

Post 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.
Last edited by infolock on Wed Oct 29, 2008 7:46 pm, edited 1 time in total.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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)
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

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

Post 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>
Post Reply