Re: Selecting time periods

Your new shift_days table has a couple of errors in it because the
"after midnight" part of shift is on next day - see below...

Michael Wood wrote:
> 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 -> 4
> 3,1
> 3,2
> 3,3
> 4,3
> 4,4
> 4,5
> 5,3
> 5,4
> 5,5
> 5,6
> 6,3 -> 0
> 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 = ?;
>