Re: Selecting time periods

On 2009-07-11, Peter Jackson <tasmaniac@iprimus.com.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 );