Re: Returning Clock Hours in Time Range
am 20.07.2006 19:40:39 von shakah
No bother wrote:
> I have a table such as below:
>
> BreakID StartTime EndTime
> 1 08:00:00 10:00:00
> 2 08:00:00 10:30:00
> 3 08:00:00 11:00:00
> 4 08:30:00 11:00:00
>
> What I need to find are the whole clock hours that are wholly within
> each time range. So, in this example:
>
> 1 08:00:00
> 1 09:00:00
> 2 08:00:00
> 2 09:00:00
> 3 08:00:00
> 3 09:00:00
> 3 10:00:00
> 4 09:00:00
> 4 10:00:00
>
> Just to be clear, when I say clock hour I mean a 60 minute period that
> starts at one of the hours universally recognized for designating 1/24
> segments of the day. E.g. 8:00 AM, not 8:01 or 8:30. I am sure there
> is some technical terminology for this, but it escapes me at the moment.
How about:
CREATE TABLE the_hours (
hour_id integer NOT NULL
,start_time time NOT NULL
,end_time time NOT NULL
) ;
INSERT INTO the_hours VALUES( 0, '00:00:00', '01:00:00') ;
INSERT INTO the_hours VALUES( 1, '01:00:00', '02:00:00') ;
INSERT INTO the_hours VALUES( 2, '02:00:00', '03:00:00') ;
INSERT INTO the_hours VALUES( 3, '03:00:00', '04:00:00') ;
INSERT INTO the_hours VALUES( 4, '04:00:00', '05:00:00') ;
INSERT INTO the_hours VALUES( 5, '05:00:00', '06:00:00') ;
INSERT INTO the_hours VALUES( 6, '06:00:00', '07:00:00') ;
INSERT INTO the_hours VALUES( 7, '07:00:00', '08:00:00') ;
INSERT INTO the_hours VALUES( 8, '08:00:00', '09:00:00') ;
INSERT INTO the_hours VALUES( 9, '09:00:00', '10:00:00') ;
INSERT INTO the_hours VALUES(10, '10:00:00', '11:00:00') ;
INSERT INTO the_hours VALUES(11, '11:00:00', '12:00:00') ;
INSERT INTO the_hours VALUES(12, '12:00:00', '13:00:00') ;
INSERT INTO the_hours VALUES(13, '13:00:00', '14:00:00') ;
INSERT INTO the_hours VALUES(14, '14:00:00', '15:00:00') ;
INSERT INTO the_hours VALUES(15, '15:00:00', '16:00:00') ;
INSERT INTO the_hours VALUES(16, '16:00:00', '17:00:00') ;
INSERT INTO the_hours VALUES(17, '17:00:00', '18:00:00') ;
INSERT INTO the_hours VALUES(18, '18:00:00', '19:00:00') ;
INSERT INTO the_hours VALUES(19, '19:00:00', '20:00:00') ;
INSERT INTO the_hours VALUES(20, '20:00:00', '21:00:00') ;
INSERT INTO the_hours VALUES(21, '21:00:00', '22:00:00') ;
INSERT INTO the_hours VALUES(22, '22:00:00', '23:00:00') ;
INSERT INTO the_hours VALUES(23, '23:00:00', '24:00:00') ;
CREATE TABLE breaks (
break_id integer NOT NULL
,start_time time NOT NULL
,end_time time NOT NULL
) ;
INSERT INTO breaks VALUES(1, '08:00:00', '10:00:00') ;
INSERT INTO breaks VALUES(2, '08:00:00', '10:30:00') ;
INSERT INTO breaks VALUES(3, '08:00:00', '11:00:00') ;
INSERT INTO breaks VALUES(4, '08:30:00', '11:00:00') ;
-- ...either should work (?)
SELECT b.break_id, h.start_time
FROM breaks b
INNER JOIN the_hours h
ON b.start_time <= h.start_time
AND b.end_time >= h.end_time
ORDER BY 1,2 ;
SELECT b.break_id, h.start_time
FROM the_hours h
INNER JOIN breaks b
ON b.start_time <= h.start_time
AND b.end_time >= h.end_time
ORDER BY 1,2 ;
?