UTC houroffset -> days_start AT TIME ZONE x - Mailing list 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;