Skip site navigation (1) Skip section navigation (2)

Re: Temporal Units

From: Listmail <lists(at)peufeu(dot)com>
To: "Rich Shepard" <rshepard(at)appl-ecosys(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Temporal Units
Date: 2007-04-29 21:06:42
Message-ID: op.trkcxgbszcizji@apollo13 (view raw, whole thread or download thread mbox)
Lists: 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...

In response to


pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group