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?
Yeah, that's the problem.
An Interval in the mathematical sense is a range (say, [1...2], or
[monday 10AM ... thursday 10PM]) which means two known endpoints. INTERVAL
in postgres is simply a convenient way to express a time difference in a
way which is very helpful to solve practical problems (ie. today + '1
month' behaves as expected whatever the current month), but which might
not be suited to your problem.
I see your shifts as ranges expressed over a modulo something set :
example, morning shift, day shift, night shift, are [ begin hour .. end
hour ] modulo 24 hour, since they repeat every day. Work days are [monday
.. friday] modulo 7 days.
Postgres intervals can't express this, since they have no fixed beginning
or end points, they are simply differences.
So if you want to know how many times a thing has been monitored each
month, maybe count(*) GROUP BY EXTRACT( month FROM monitoring_time ); same
thing for week and weekdays, and more funky formulations will be needed
for shifts...
