Обсуждение: Dates rejected

Carlos Reimer

Carlos H. Reimer <carlos.reimer@opendb.com.br> schrieb:

>
> Hi,
>
> WeŽve a simple insert that is not working. The strange thing is that all kind
> of date are working with the exception of 15/10 (DD/MM) dates.
>
> create table tt_teste (datfis timestamp without time zone not null
>  CHECK (datfis = trunc(datfis::timestamp without time zone)));
                   ^^^^^

we don't have such a function, perhaps date_trunc()?


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Mon, Oct 16, 2006 at 12:22:04PM -0200, Carlos H. Reimer wrote:
> Hi,
>
> We´ve a simple insert that is not working. The strange thing is that all
> kind of date are working with the exception of 15/10 (DD/MM) dates.
>
> create table tt_teste (datfis timestamp without time zone not null
>  CHECK (datfis = trunc(datfis::timestamp without time zone)));

What are you trying to do here? If you only want a date, why not just
use a date type?

Have you tried evaluating the expression yourself?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout <kleptog@svana.org> schrieb:
> > create table tt_teste (datfis timestamp without time zone not null
> >  CHECK (datfis = trunc(datfis::timestamp without time zone)));
>
> What are you trying to do here? If you only want a date, why not just
> use a date type?

This is an other question ;-)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Hi,

I don´t know why the developers build in this way... but let me change a
little bit my question.

I´ve executed tree to_date functions but they give a strange answer for date
16/10/2006 (DD/MM/YYYY).

select to_date('16/10/2006','DD/MM/YYYY');
       to_date
---------------------
 2006-10-16 00:00:00
(1 row)

select to_date('15/10/2006','DD/MM/YYYY');
       to_date
---------------------
 2006-10-15 01:00:00
(1 row)

select to_date('14/10/2006','DD/MM/YYYY');
       to_date
---------------------
 2006-10-14 00:00:00
(1 row)

How can we explain the 01:00:00 hour that the to_date function returns for
date 15/10/2006?

Thank you!

Carlos


> -----Mensagem original-----
> De: Andreas Kretschmer,,, [mailto:andreas@a-kretschmer.de]Em nome de
> Andreas Kretschmer
> Enviada em: segunda-feira, 16 de outubro de 2006 13:41
> Para: pgsql-general@postgresql.org
> Cc: Carlos H. Reimer
> Assunto: Re: [GENERAL] Dates rejected
>
>
> Martijn van Oosterhout <kleptog@svana.org> schrieb:
> > > create table tt_teste (datfis timestamp without time zone not null
> > >  CHECK (datfis = trunc(datfis::timestamp without time zone)));
> >
> > What are you trying to do here? If you only want a date, why not just
> > use a date type?
>
> This is an other question ;-)
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.                              (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."    (unknow)
> Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
>
>

"Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> select to_date('16/10/2006','DD/MM/YYYY');
>        to_date
> ---------------------
>  2006-10-16 00:00:00
> (1 row)

Um... what have you done to to_date()?  The standard version returns a
date, not a timestamp:

regression=# select to_date('15/10/2006','DD/MM/YYYY');
  to_date
------------
 2006-10-15
(1 row)


            regards, tom lane

On Oct 16, 2006, at 1:08 PM, Carlos H. Reimer wrote:

> How can we explain the 01:00:00 hour that the to_date function
> returns for
> date 15/10/2006?

does your timezone change from summer time to winter time (daylight
savings, etc.) on that date?

Hi Tom,

You are right, I´ve discovered that the to_date was changed to return a
timestamp, the original function is returning the right values.

The to_date I´ve found:
CREATE OR REPLACE FUNCTION PUBLIC.TO_DATE(text, text) RETURNS TIMESTAMP
AS '
BEGIN
 RETURN pg_Catalog.TO_TIMESTAMP($1,$2);
END;
' language 'plpgsql';

I don´t know why they have changed it but anyway why is this changed
function returning 01:00:00 in the hour field only for the date 15/10/2006
(DD/MM/YYYY)?

It started happening in the first day when Linux has changed to the day
light time (15/10/2006).

Thanks in advance!

Carlos


> -----Mensagem original-----
> De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Enviada em: segunda-feira, 16 de outubro de 2006 16:27
> Para: carlos.reimer@opendb.com.br
> Cc: Andreas Kretschmer; Pgsql-General@Postgresql.Org
> Assunto: Re: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> > select to_date('16/10/2006','DD/MM/YYYY');
> >        to_date
> > ---------------------
> >  2006-10-16 00:00:00
> > (1 row)
>
> Um... what have you done to to_date()?  The standard version returns a
> date, not a timestamp:
>
> regression=# select to_date('15/10/2006','DD/MM/YYYY');
>   to_date
> ------------
>  2006-10-15
> (1 row)
>
>
>             regards, tom lane
>
>

Hi,

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.

template1=# select pg_catalog.to_timestamp('15/10/2006','dd/mm/yyyy') as
date;
          date
------------------------
 2006-10-15 01:00:00-02
(1 row)

Why is this offset present only for date 15/10/2006 (DD/MM/YYY)?

Carlos


> -----Mensagem original-----
> De: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]Em nome de Tom Lane
> Enviada em: segunda-feira, 16 de outubro de 2006 16:27
> Para: carlos.reimer@opendb.com.br
> Cc: Andreas Kretschmer; Pgsql-General@Postgresql.Org
> Assunto: Re: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> > select to_date('16/10/2006','DD/MM/YYYY');
> >        to_date
> > ---------------------
> >  2006-10-16 00:00:00
> > (1 row)
>
> Um... what have you done to to_date()?  The standard version returns a
> date, not a timestamp:
>
> regression=# select to_date('15/10/2006','DD/MM/YYYY');
>   to_date
> ------------
>  2006-10-15
> (1 row)
>
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>

"Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:

> How can we explain the 01:00:00 hour that the to_date function returns for
> date 15/10/2006?

You haven't fixed your configuration and your machine is considering that
you're in DST.

Lots of machines here in Brazil that weren't updated / fixed by their
administrators are showing this problem.

--
Jorge Godoy      <jgodoy@gmail.com>

"Carlos H. Reimer" <carlos.reimer@opendb.com.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

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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]Em nome de Tom Lane
> Enviada em: segunda-feira, 16 de outubro de 2006 21:38
> Para: carlos.reimer@opendb.com.br
> Cc: Andreas Kretschmer; Pgsql-General@Postgresql.Org
> Assunto: Re: RES: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <carlos.reimer@opendb.com.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
>
>

"Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> To discover if it works this way I�ve changed the /etc/localtime to relect
> the following timezone:

Um ... what PG version are you working with?  8.0 and up don't pay
attention to /etc/localtime, because they have their own timezone info.

            regards, tom lane

Yes, it is the 8.0.8 version.

You mean that changes to /etc/localtime should not reflect in the
to_timestamp behavior? Strange, why does to_timestamp behavior changed here
when the /etc/localtime was overlaped?

Well, maybe the PG refresh I´ve done after changing the /etc/localtime did
it (pg_ctl restart).

Anyway, if it will not look at the /etc/localtime, how PG will discover the
DST changes?

Thank you in advance!

Carlos


> -----Mensagem original-----
> De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Enviada em: terça-feira, 17 de outubro de 2006 00:02
> Para: carlos.reimer@opendb.com.br
> Cc: Pgsql-General@Postgresql.Org
> Assunto: Re: RES: RES: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> > To discover if it works this way I´ve changed the
> /etc/localtime to relect
> > the following timezone:
>
> Um ... what PG version are you working with?  8.0 and up don't pay
> attention to /etc/localtime, because they have their own timezone info.
>
>             regards, tom lane
>
>

Hi Tom,

I think I´ve got it...

If you change something in the timezone file that is specified in
postgresq.conf, PG will know this changes automatically? Am I right?

Carlos

> -----Mensagem original-----
> De: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]Em nome de Tom Lane
> Enviada em: terça-feira, 17 de outubro de 2006 00:02
> Para: carlos.reimer@opendb.com.br
> Cc: Pgsql-General@Postgresql.Org
> Assunto: Re: RES: RES: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> > To discover if it works this way I´ve changed the
> /etc/localtime to relect
> > the following timezone:
>
> Um ... what PG version are you working with?  8.0 and up don't pay
> attention to /etc/localtime, because they have their own timezone info.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>
>