From: Steve Crawford <scrawford@pinpointresearch.com>
To: Edward W. Rouse <erouse@comsquared.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: time interval math
Date: Wed, 08 Feb 2012 12:25:31 -0800
Message-ID: <4F32DA3B.9080209@pinpointresearch.com> (raw)
In-Reply-To: <032001cce69c$767bc010$63734030$@com>
References: <032001cce69c$767bc010$63734030$@com>
On 02/08/2012 12:01 PM, Edward W. Rouse wrote:
> I'm still working on getting this to work, but the summary is this:
>
> I am getting several (many) intervals of hour, minutes and seconds. I need a
> sum of the absolute value these intervals, similar to the SUM(ABS())
> function for numbers; and I need to divide this sum by an integer (bigint).
> Getting the intervals is no problem, but I can't find built in functions for
> the rest. Currently on 8.3, want to upgrade to 9.x but I can't until this is
> finished.
>
> Do these functions exist, or will I be forced to convert to seconds, do the
> math and then convert back to hour-minute-second format (I am assuming from
> current data that, after the divide, the result should be in the minute:
> second range).
You will have to do some work on your own.
Time and intervals are tricky beasts and depend on the oddities of
daylight saving rules. Even though you are only using
hours/minutes/seconds the interval type also supports days and months. A
day interval is probably 24 hours but could be 23 or 25 if it crosses a
DST boundary. Months have different numbers of days. You have situations
where adding and subtracting a month does not give the original date:
select '2011-03-31'::date - '1 month'::interval + '1 month'::interval;
?column?
---------------------
2011-03-28 00:00:00
There is no abs(interval) function but, if you know that all your
intervals are basic H:M:S and that you won't have any difficulty due to
problems similar to the above you can mimic it with:
...case when myinterval < '0'::interval then '0'::interval - myinterval
else myinterval end...
You are even allowed to sum that and divide it (though I suspect there
are some interesting corner-cases waiting to be discovered):
...sum(case when myinterval < '0'::interval then '0'::interval -
myinterval else myinterval end)/2...
Before you upgrade, be sure to read the release notes and test your
calculations. The way intervals are handled, especially regarding
intervals across DST boundaries, have changed over time. IIRC most of
those changes were pre-8.3 but haven't looked recently.
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, erouse@comsquared.com
Subject: Re: time interval math
In-Reply-To: <4F32DA3B.9080209@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox