From: "Edward W. Rouse" <erouse@comsquared.com>
To: <pgsql-sql@postgresql.org>
References: <032001cce69c$767bc010$63734030$@com>
	<jh0b4p$ruj$2@reversiblemaps.ath.cx>
In-Reply-To: <jh0b4p$ruj$2@reversiblemaps.ath.cx>
Subject: Re: time interval math
Date: Thu, 9 Feb 2012 09:31:34 -0500
Message-ID: <034901cce737$86614180$9323c480$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AcznIrbgpfT1jkH3T8e6BWCZjt9KOgAFH7AQ
Content-Language: en-us


> -----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
>=20
> 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.
>=20
> 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.=20

>=20
> 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.
>=20
> > 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).
>=20
> Yeah, you need to do that, it's not hard,
>=20
> select ( sum(abs(extract('epoch' from YOUR_INTERVAL ))) / SOMETHING ) =
*
> '1s'::interval
> from ...
>=20
> --
> =E2=9A=82=E2=9A=83 100% natural
>=20
>=20
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql