Re: Re: to_timestamp returns the incorrect result for the DST fall over time.

On Wed, Jan 5, 2011 at 6:10 AM, Gouse <gkhaji(at)gmail(dot)com> wrote:
> // snippet of the stored procedure...
> CREATE OR REPLACE FUNCTION wf_to_timestamp("varchar")
>  RETURNS timestamptz AS
> $BODY$
>  select to_timestamp($1,'YYYY-MM-DD HH24:MI:SS.MS')+ interval '1 hour' *
> (extract(timezone from to_timestamp($1,'YYYY-MM-DD HH24:MI:SS.MS')) /
> 3600.0)
> $BODY$
>  LANGUAGE 'sql' VOLATILE;
> ALTER FUNCTION wf_to_timestamp("varchar") OWNER TO postgres;
>
> When I pass the UTC time '2010-03-28 01:00:03 243'  I should expect the
> output to be '2010-03-28 02:00:03.243 + 01'  , instead I am getting the
> result '2010-03-28 03:00:03.243 + 01'.
>
> The store procedure works well for the UTC time starting from '2010-03-28
> 02:00:00 000'. The problem only lies for the UTC time period  from
> '2010-03-28 01:00:00 000' to '2010-03-28 01:59:59 999'.

How about something like this:

create or replace function x(text) returns timestamptz as $$select
to_timestamp($1,'YYYY-MM-DD HH24:MI:SS.MS')$$ set timezone = 'UTC'
language sql;

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company