UTC houroffset -> days_start AT TIME ZONE x

From: Marc Mamin <M(dot)Mamin(at)intershop(dot)de>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: UTC houroffset -> days_start AT TIME ZONE x
Date: 2013-06-17 11:45:41
Message-ID: B6F6FD62F2624C4C9916AC0175D56D880CDFE242@jenmbs01.ad.intershop.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
I have time columns, whereas the time ist stored as houroffset in epoch.
e.g 36089 =>
select '19700101 00:01:00 GMT'::timestamptz + interval '360089' hours'
=> 2011-01-29 18:01:00+01

Now I want an aggregation that sum my values on the day start in a given time zone.
The function below works, but is slow.
Any way to build an equivalent function with better performances ?

Thanks,
Marc Mamin

CREATE FUNCTION houroffset_to_daystart (p_houroffset int, p_tz varchar) returns int AS
$$
DECLARE daystart int;

BEGIN

EXECUTE 'select EXTRACT (''epoch'' FROM
date_trunc(''day'',(''19700101 00:01:00 GMT''::timestamptz + interval '''||p_houroffset||' hours'')
AT TIME ZONE '''||p_tz||''')
)/3600'
INTO daystart;
RETURN daystart;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

Browse pgsql-general by date

  From Date Subject
Next Message jmfox180 2013-06-17 17:44:44 Re: pg_upgrade only to 9.0 ?
Previous Message Stuart Bishop 2013-06-17 11:25:59 Better dual WAL shipping/streaming integration?