Skip site navigation (1) Skip section navigation (2)

Re: Selecting time periods

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Selecting time periods
Date: 2009-07-18 14:43:09
Message-ID: h3sn1t$thg$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-novice
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 );  





In response to

pgsql-novice by date

Next:From: ...tharasDate: 2009-07-19 03:07:02
Subject: Re: Rate of increase/decrease for a set of values
Previous:From: Jasen BettsDate: 2009-07-18 14:11:31
Subject: Re: Rate of increase/decrease for a set of values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group