Re: Temporal Units

On Sun, 29 Apr 2007 22:33:37 +0200, Rich Shepard
<rshepard(at)appl-ecosys(dot)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?

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...