Thread: UTC houroffset -> days_start AT TIME ZONE x

UTC houroffset -> days_start AT TIME ZONE x

From
Marc Mamin
Date:
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;