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