From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: time interval math
Date: 9 Feb 2012 11:36:57 GMT
Message-ID: <jh0b4p$ruj$2@reversiblemaps.ath.cx> (raw)
References: <032001cce69c$767bc010$63734030$@com>
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
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: jasen@xnet.co.nz
Subject: Re: time interval math
In-Reply-To: <jh0b4p$ruj$2@reversiblemaps.ath.cx>
* 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