Re: Selecting time periods

On 2009-07-11, Peter Jackson <tasmaniac(at)iprimus(dot)com(dot)au> wrote:
> Hiya List,
>
> OK I'm stumped. More than likely this is easy but I cant work it out.
>
> Debian 4
> Postgresql 8.3.1
>
> I'm trying to insert some records into various tables (that bit works
> ok) but now I want to have another table that records the shift that the
> record was inserted on.
>
> table shift_times ( shift_id pk, shift text, start time, finish time,
> days varchar[])
>
> data
> 1 A 7:00 19:00 {0,1,2}
> 2 B 19:00 7:00 {0,1,2,3}
> 3 C 7:00 19:00 {3,4,5}
> 4 D 19:00 7:00 {3,4,5,6}

time is an interestign quantity.

I note that shifts B and D run concurrently on day 3

> Now what I want to do is basically just select the shift and insert
> some data into another table linking shift and record_id etc.
>
> My problem is trying to workout the select statement for getting the
> shift.

try this:

-- eg '17:00' on day '3'

select shift from shift times where
( '17:00'::time between start and finish or
finish < start and not '17:00'::time between finish and start );