Re: Selecting time periods

2009/7/11 Frank Bax <fbax(at)sympatico(dot)ca>:
> 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.

That would probably be how I'd do it too.

Also, instead of storing an array of days in the "days" column, I'd
have another table to store the days linked to the shift table:

shift:
id,name,start,finish
1,A,07:00,18:59
2,B,19:00,23:59
3,B,00:00,06:59
4,C,07:00,18:59
5,D,19:00,23:59
6,D,19:00,05:59

(If you do this you will have to keep the "B"s and the "D"s in sync if
they are updated.)

shift_days:
shift_id,day
1,0
1,1
1,2
2,0
2,1
2,2
2,3
3,0
3,1
3,2
3,3
4,3
4,4
4,5
5,3
5,4
5,5
5,6
6,3
6,4
6,5
6,6

Then your query could look something like this:

SELECT name FROM shift WHERE ? BETWEEN start AND <= finish AND
shift.id = shift_days.shift_id AND shift_days = ?;

--
Michael Wood <esiotrot(at)gmail(dot)com>