Re: Temporal Units

Listmail wrote:
> On Sun, 29 Apr 2007 22:33:37 +0200, Rich Shepard
> <rshepard@appl-ecosys.com> wrote:
>
>> On Sun, 29 Apr 2007, Martijn van Oosterhout wrote:
>>
>>> Then I'm afraid you havn't indicated your requirements properly. All
>>> I  can
>>> see is that the interval type does exactly what you want. It can store
>>> days, weeks, months or any combination thereof. You can multiply
>>> them  and
>>> add them to dates and all works as expected.
>>
>>    How does one define 'shift' with intervals? 0.33 DAY?
>
If I'm following this correctly, then interval & extract timepart can be
used to provide all the required functionality:

If you know what hours your shifts (periods) cover, and you want to
check that you have a value for that shift today (ignoring for now
issues with shifts only on 5 day working weeks & holidays, ...) then you
can do something like:

if (select count(*) from table
     where extract day = today
         and extract hour (now) >= extract hour from (shift start timestamp)
         and extract hour <= extract hour from(shift start timestamp +
interval shift length)
    ) =0
then a value is missing

So for any check, you want to ensure you have no periods without a
value, which can only be done at the end of each period.
If you have a table defining each period, a cron job can run (for
example) hourly, identifying any periods which ended in the last hour
and did not have a value. Or periods about to end in the next "interval"
which do not yet have a value, to prompt before the period ends.

The trickier part is how to specify your periods, and which
days/hours/months/etc are included. Each period needs to be  defined by
data which allows a start and a finish date/time expressed in a generic
way which is relative to "now" to be determined. So for any "now" we can
evaluate which periods are about to end or have just ended.


Cheers,

   Brent Wood