[BUGS] to_timestamp returns the incorrect result for the DST fall over time.

[Gouse]

Hi 

I find that the to_timestamp giving the incorrect result for the DST time
period.


select to_timestamp('2010-03-28 01:00:03 243','-MM-DD HH24:MI:SS.MS')
results '2010-03-28 02:00:03.243 + 01'  , I am not sure if this is correct.

select to_timestamp('2010-03-28 02:00:03 243','-MM-DD HH24:MI:SS.MS')
results '2010-03-28 02:00:03.243 + 01'

Note- The clokc on the system was GMT London.  The date 2010-03-28 is the
day of the DST.
Postgres DB versoin is 8.0

Thanks,
Gouse


-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/to-timestamp-returns-the-incorrect-result-for-the-DST-fall-over-time-tp3327393p3327393.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

[Gouse]

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
$BODY$
  select to_timestamp($1,'-MM-DD HH24:MI:SS.MS')+ interval '1 hour' *
(extract(timezone from to_timestamp($1,'-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'.

Can you verifiy this pls..?


Thanks,
Gouse













-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/to-timestamp-returns-the-incorrect-result-for-the-DST-fall-over-time-tp3327393p3328589.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

[Gouse]

I am afraid this may not give the result I am looking for. The  sp x(text)
gives me the result always will be UTC time zone.
My requirement here is, that i should pass the UTC time in text as a
parameter and should return local timestamp with timzone for that UTC time.


select x('2010-03-28 01:00:03 243') should return '2010-03-28 02:00:03.243 +
01' as locks are supposed to jump from 1AM directly to 2AM when my clock is
set to GMT 

select x ('2010-03-28 02:00:03 243') should return '2010-03-28 03:00:03.243
+ 01' 
select x ('2010-03-28 03:00:03 243') should return '2010-03-28 04:00:03.243
+ 01' 

Thanx,
Gouse


-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/to-timestamp-returns-the-incorrect-result-for-the-DST-fall-over-time-tp3327393p3330384.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

[Gouse]

Now this works great. 
Thanks for the help.

Thanks,
Gouse
-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/to-timestamp-returns-the-incorrect-result-for-the-DST-fall-over-time-tp3327393p3331798.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs