Selecting time periods

From: Peter Jackson <mltasmaniac(at)tasjackson(dot)com>
To: pgsql <pgsql-novice(at)postgresql(dot)org>
Subject: Selecting time periods
Date: 2009-07-11 06:51:03
Message-ID: 4A583657.2010505@tasjackson.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Jasen Betts 2009-07-11 11:07:57 Re: cannot locate where dataase files are stored
Previous Message Peter Jackson 2009-07-11 06:50:45 Selecting time periods