Skip site navigation (1) Skip section navigation (2)

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

From: Gouse <gkhaji(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: to_timestamp returns the incorrect result for the DST fall over time.
Date: 2011-01-05 11:10:49
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
Thanks Tom for the Update.
You are correct that there is not local date and time for that DST time
period as clocks jump to 2 A.M.

The problem i am seeing  here is that, we have a small stored procedure
written which will always take UTC time as parameter and should convert it
to local timestamp with time zone. Can you verify the sp pls and let me if I
am doing something wrong.

// snippet of the stored procedure...
CREATE OR REPLACE FUNCTION wf_to_timestamp("varchar")
  RETURNS timestamptz AS
  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')) /
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'.

Can you verifiy this pls..?


View this message in context:
Sent from the PostgreSQL - bugs mailing list archive at

In response to


pgsql-bugs by date

Next:From: harshadDate: 2011-01-05 11:17:43
Subject: certification courses for postgreSQL......
Previous:From: Dave PageDate: 2011-01-05 08:54:42
Subject: Re: BUG #5808: Remove PgOleDB from Stack Builder

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group