[thelist] SQL Dates Problem

Joshua Olson joshua at waetech.com
Tue Apr 12 08:28:18 CDT 2005


> -----Original Message-----
> From: Burhan Khalid
> Sent: Tuesday, April 12, 2005 9:03 AM
> 
> Hello Everyone:
> 
>    I'm sure there is a simple way to do this, but I've been 
> staring at 
> the problem too long and I can't get out of this mental rut.
> 
>    In the simplest case, there are three tables, halls, events, and 
> halls_events.
> 
>    halls = id, name, description
>    events = id, name, start_date, end_date (both are integer 
> timestamps)
>    halls_events = id, hallid, eventid
> 
>    I can't figure out the best way to find out the earliest 
> availablity 
> date for each hall.  The earliest date is one day from the latest end 
> date for an event in a particular hall.

Burhan,

Here's an approach that may work for you:

1.  Convert the integer timestamps to date fields so that we can use them as
comparison.  Make sure the time portion of the date field is midnight.
2.  Enumerate all possible days (from today to a reasonably far date in the
future) in another table.
3.  Join with the enumeration table to determine the minimum available date
for each hall that doesn't have an event. *

* obviously I'm leaving out the details of the join syntax, which shouldn't
be too bad.  The question posted yesterday entitled "MySQL Grouping problem"
is going to play a pivotal role in this particular technique.

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168 




More information about the thelist mailing list