Thread: time interval math
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). Edward W. Rouse Comsquared System, Inc. 770-734-5301
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
Hehe, thanks, I played around and ended up with this:
round(SUM(extract('epoch' from (time_out - time_in))))
I will have to do the division outside of the query, but that's really a
minor issue. Knowing the total in seconds was the big roadblock. And
converting back is easier (a / 3600 + ":" + a / 60 + ":" + a % 60)
> -----Original Message-----
> From: Steve Crawford [mailto:scrawford@pinpointresearch.com]
> Sent: Wednesday, February 08, 2012 3:26 PM
> To: Edward W. Rouse
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] time interval math
>
> 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
"Edward W. Rouse" <erouse@comsquared.com> wrote:
> Hehe, thanks, I played around and ended up with this:
> round(SUM(extract('epoch' from (time_out - time_in))))
> I will have to do the division outside of the query, but that's really a
> minor issue.
You can always use subqueries.
> Knowing the total in seconds was the big roadblock. And
> converting back is easier (a / 3600 + ":" + a / 60 + ":" + a % 60)
> [...]
PostgreSQL has also:
| tim=# SELECT 3661::TEXT::INTERVAL;
| interval
| ----------
| 01:01:01
| (1 Zeile)
| tim=#
Tim
On 2012-02-08, Edward W. Rouse <erouse@comsquared.com> 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.
the operation abs() is meaninless on the type interval
eg: what is abs( '1 month - 32 days + 24 hours'::interval )
howevwer since all your intervals are in seconds (postgres pretends that all
hours are 3600 seconds long) converting to seconds is probably the
best way to go.
> 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).
Yeah, you need to do that, it's not hard,
select ( sum(abs(extract('epoch' from YOUR_INTERVAL ))) / SOMETHING ) * '1s'::interval
from ...
--
⚂⚃ 100% natural
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Jasen Betts
> Sent: Thursday, February 09, 2012 6:37 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] time interval math
>
> On 2012-02-08, Edward W. Rouse <erouse@comsquared.com> 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.
>
> the operation abs() is meaninless on the type interval
> eg: what is abs( '1 month - 32 days + 24 hours'::interval )
If you need to add 30 intervals together, then +- is not meaningless.
>
> howevwer since all your intervals are in seconds (postgres pretends
> that all
> hours are 3600 seconds long) converting to seconds is probably the
> best way to go.
>
> > 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).
>
> Yeah, you need to do that, it's not hard,
>
> select ( sum(abs(extract('epoch' from YOUR_INTERVAL ))) / SOMETHING ) *
> '1s'::interval
> from ...
>
> --
> ⚂⚃ 100% natural
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
On 2012-02-09, Edward W. Rouse <erouse@comsquared.com> wrote: >> the operation abs() is meaninless on the type interval >> eg: what is abs( '1 month - 32 days + 24 hours'::interval ) > > If you need to add 30 intervals together, then +- is not meaningless. if you stop reading after one line you miss the answer. -- ⚂⚃ 100% natural