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

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','YYYY-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','YYYY-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.

Gouse <gkhaji@gmail.com> writes:
> I find that the to_timestamp giving the incorrect result for the DST time
> period.

On what grounds do you claim it's incorrect?

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

The reason it does that is that actually there *is* no such local time
as 1:00:03 on that date.  Clocks are supposed to jump from 1AM directly
to 2AM.  PG's actual behavior is that the bogus time is interpreted as
standard time (UTC+0 in your case).  The only other thing it could
plausibly do is throw an error, and that has been judged to be less
useful.

            regards, tom lane

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,'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'.

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.

On Wed, Jan 5, 2011 at 6:10 AM, Gouse <gkhaji@gmail.com> wrote:
> // snippet of the stored procedure...
> CREATE OR REPLACE FUNCTION wf_to_timestamp("varchar")
> =A0RETURNS timestamptz AS
> $BODY$
> =A0select 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$
> =A0LANGUAGE 'sql' VOLATILE;
> ALTER FUNCTION wf_to_timestamp("varchar") OWNER TO postgres;
>
> When I pass the UTC time '2010-03-28 01:00:03 243' =A0I should expect the
> output to be '2010-03-28 02:00:03.243 + 01' =A0, 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 =A0from
> '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 =3D 'UTC'
language sql;

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

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.

Gouse <gkhaji@gmail.com> wrote:

> I am afraid this may not give the result I am looking for.

It sounds like you didn't even try it.  That's bad form.

I'm wondering why you bring to_timestamp into it at all, though.
Are you aware of the behavior of casting?:

test=# select '2011-01-15 00:00:00.0 UTC'::timestamptz;
      timestamptz
------------------------
 2011-01-14 18:00:00-06
(1 row)

If you put together a string with the UTC timezone specified, and
cast it to timestamptz, I think you'll get what you want.

-Kevin

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.