Re: time interval math

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: time interval math
Date: 2012-02-09 11:36:57
Message-ID: jh0b4p$ruj$2@reversiblemaps.ath.cx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

On 2012-02-08, Edward W. Rouse <erouse(at)comsquared(dot)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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Edward W. Rouse 2012-02-09 14:31:34 Re: time interval math
Previous Message ssylla 2012-02-09 02:31:19 update column with multiple values