Re: Selecting time periods
От | Frank Bax |
---|---|
Тема | Re: Selecting time periods |
Дата | |
Msg-id | 4A588273.9040506@sympatico.ca обсуждение исходный текст |
Ответ на | Selecting time periods (Peter Jackson <mltasmaniac@tasjackson.com>) |
Ответы |
Re: Selecting time periods
|
Список | pgsql-novice |
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.
В списке pgsql-novice по дате отправления: