Thread: time interval math

time interval math

From
"Edward W. Rouse"
Date:
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





Re: time interval math

From
Steve Crawford
Date:
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


Re: time interval math

From
"Edward W. Rouse"
Date:
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



Re: time interval math

From
Tim Landscheidt
Date:
"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



Re: time interval math

From
Jasen Betts
Date:
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



Re: time interval math

From
"Edward W. Rouse"
Date:

> -----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



Re: time interval math

From
Jasen Betts
Date:
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