Re: Selecting time periods

Peter Jackson wrote:
> table shift_times ( shift_id pk, shift text, start time, finish time,
> days varchar[])
>
> 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}
>
> 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.

The problem with "B" and "D" is that start > end so "between start and
finish" is never true. You need something like:

SELECT shift from shift_times where CASE WHEN start < finish THEN
'19:30' between start AND finish ELSE '19:30' between start and '23:59'
or '19:30' between '00:00' and finish END;

I have an application (payroll time sheets) with a similar reporting
requirement. In my tables; start and end of attendance record cannot
cross midnight; when this happens, two entries are made into tables -
one for each day. This simple restriction on data entry makes most of
the reporting *much* easier.