Listmail wrote:
On Sun, 29 Apr 2007 22:33:37 +0200, Rich Shepard
<rshepard@xxxxxxxxxxxxxxx> 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
