Skip site navigation (1) Skip section navigation (2)

Re: Selecting time periods

From: Michael Wood <esiotrot(at)gmail(dot)com>
To: Peter Jackson <mltasmaniac(at)tasjackson(dot)com>
Cc: PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Selecting time periods
Date: 2009-07-11 13:13:38
Message-ID: 5a8aa6680907110613u3c7a428cg609b367c7e88d5ff@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
2009/7/11 Michael Wood <esiotrot(at)gmail(dot)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(at)gmail(dot)com>

In response to

Responses

pgsql-novice by date

Next:From: Michael WoodDate: 2009-07-11 13:16:43
Subject: Re: Selecting time periods
Previous:From: Michael WoodDate: 2009-07-11 13:07:31
Subject: Re: Selecting time periods

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group