Re: Temporal Units
От | Listmail |
---|---|
Тема | Re: Temporal Units |
Дата | |
Msg-id | op.trkcxgbszcizji@apollo13 обсуждение исходный текст |
Ответ на | Re: Temporal Units (Rich Shepard <rshepard@appl-ecosys.com>) |
Ответы |
Re: Temporal Units
|
Список | pgsql-general |
On Sun, 29 Apr 2007 22:33:37 +0200, Rich Shepard <rshepard@appl-ecosys.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 по дате отправления: