Обсуждение: Selecting time periods

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}

  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.

  I've tried SELECT shift from shift_times where '17:00' between start
AND finish;
  Which works for A and C but if I change the 17:00 to 19:30 it returns
no rows.

  I havent even tried thinking on the days yet.
  As this is only in a test dbase I can change the table/s etc if need be

I realize I could do it with if/else statements on the trigger/s but I
want to make it as easy as possible for someone else to change the shift
start/finish times/days.


Anyway if anyone has ideas or can point me to a web page/etc it would be
appreciated.

Peter Jackson

> From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-
> owner@postgresql.org] On Behalf Of Peter Jackson
> Subject: [NOVICE] Selecting time periods

>   I've tried SELECT shift from shift_times where '17:00' between start
> AND finish;
>   Which works for A and C but if I change the 17:00 to 19:30 it
returns
> no rows.

Just taking a guess here, but how about:

SELECT shift FROM shift_times WHERE '19:30' BETWEEN least(start,finish)
AND greatest(start,finish);

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
.now.

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 );