Обсуждение: 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
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.
2009/7/11 Frank Bax <fbax@sympatico.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@gmail.com>
2009/7/11 Michael Wood <esiotrot@gmail.com>:
>>> 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:
Sorry, I messed up the shift table. This is what I meant:
> shift:
id name start finish
1 A 07:00 18:59
2 B 19:00 06:59
3 B 19:00 06:59
4 C 07:00 18:59
5 D 19:00 06:59
6 D 19:00 06:59
--
Michael Wood <esiotrot@gmail.com>
Third time lucky. Sorry, I think I need more sleep. 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 00:00 06:59 -- Michael Wood <esiotrot@gmail.com>
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@sympatico.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 = ?;
>
2009/7/12 Frank Bax <fbax@sympatico.ca>: > 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... Yes, I realised that later, but didn't want to send yet another correction ;) -- Michael Wood <esiotrot@gmail.com>