RES: RES: RES: Dates rejected

Hi Tom,

Thank you very much for your explanation!

Let me know if I´ve understood correctly:

If I move the first day DST from Oct 15th to Nov 05th, then the to_timestamp
should show the offset on day Nov 05th and not anymore on Oct 15th, right?

To discover if it works this way I´ve changed the /etc/localtime to relect
the following timezone:
Rule Brazil 2006 only - Nov 05 00:00 1 S
Rule Brazil 2007 only - Feb 25 00:00 0 -

Zone Brazil/hv2006 -3:00 Brazil BR%sT

And tried:
template1=# select pg_catalog.to_timestamp('15/10/2006','dd/mm/yyyy');
to_timestamp
------------------------
2006-10-15 00:00:00-03
(1 row)

Ok, the result now is reflecting the DST changing of the timezone.

Another tried:
template1=# select pg_catalog.to_timestamp('05/11/2006','dd/mm/yyyy');
to_timestamp
------------------------
2006-11-05 00:00:00-03
(1 row)

Should it not show 2006-11-05 01:00:00-02 as happened before with date
15/10/2006 (dd/mm/yyyy)?

Am I missing something?

Thanks in advance!

Carlos

> -----Mensagem original-----
> De: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]Em nome de Tom Lane
> Enviada em: segunda-feira, 16 de outubro de 2006 21:38
> Para: carlos(dot)reimer(at)opendb(dot)com(dot)br
> Cc: Andreas Kretschmer; Pgsql-General(at)Postgresql(dot)Org
> Assunto: Re: RES: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <carlos(dot)reimer(at)opendb(dot)com(dot)br> writes:
> > The problem is related with the to_timestamp function that
> returns +1 hour
> > offset only for the date 15/10/2006. The 15th october is the
> first day of
> > our day light change.
>
> The reason is that it's generating '2006-10-15 00:00:00-03' to start
> with, but there *is* no such time in your time zone: that was the
> instant that the clocks stepped forward, and so it's equally legitimate
> to display that time as '2006-10-15 01:00:00-02', which is what in fact
> our code happens to do. Then when you coerce the timestamp with time
> zone down to plain timestamp, the offset info that might have cued you
> what's going on goes away...
>
> I believe the lack of a definite midnight hour is one reason why most
> countries prefer to change their clocks at some other time of night.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>