## Re: Temporal Units

From: Listmail "Rich Shepard" , pgsql-general(at)postgresql(dot)org Re: Temporal Units 2007-04-29 21:06:42 op.trkcxgbszcizji@apollo13 (view raw or whole thread) 2007-04-28 21:44:32 from Rich Shepard  2007-04-28 23:36:06 from "Alexander Staubo"  2007-04-29 00:14:01 from "Alexander Staubo"   2007-04-29 02:41:42 from Rich Shepard    2007-04-29 04:58:26 from Tom Lane     2007-04-29 14:43:52 from Rich Shepard      2007-04-29 20:02:39 from Martijn van Oosterhout       2007-04-29 20:33:37 from Rich Shepard        2007-04-29 20:45:53 from Rich Shepard        2007-04-29 21:06:42 from Listmail         2007-04-30 00:57:14 from Brent Wood          2007-04-30 13:15:52 from Rich Shepard           2007-04-30 14:09:11 from "John D(dot) Burger"            2007-04-30 16:05:04 from Rich Shepard             2007-05-03 00:26:52 from Lew              2007-05-04 01:10:41 from Rich Shepard           2007-04-30 15:48:26 from Ted Byers            2007-04-30 16:14:45 from Rich Shepard             2007-04-30 20:05:25 from Ted Byers pgsql-general
```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...

```

### pgsql-general by date

 Next: From: chrisj Date: 2007-04-29 22:35:48 Subject: Re: tsearch2 and english locale on Debian - english.0 english.1 Previous: From: Vinay Sajip Date: 2007-04-29 20:55:13 Subject: Re: pg_dump without psql rights