PostgreSQL Bugs - BUG #1653: Bug in date

Author

BUG #1653: Bug in date

Guilherme

2005-05-06, 8:24 pm

The following bug has been logged online:

Bug reference: 1653
Logged by: Guilherme
Email address: guilherme.silva@nexxera.com
PostgreSQL version: 7.4
Operating system: Linux
Description: Bug in date
Details:

Dear Postgres Support,

I think found a bug in postgres, see the sql:

BILHETAGEM=# select sum(tm_arquivo) from bilhete where
cd_caixa_postal_prin
cipal=1304 AND
BILHETAGEM-# dt_transacao >= to_date('01042005000
000', 'ddmmyyyyHH24MISS')
AND
BILHETAGEM-# dt_transacao < to_date('02042005000
000', 'ddmmyyyyHH24MISS');

sum
-----
968
(1 row)

BILHETAGEM=# select sum(tm_arquivo) from bilhete where
cd_caixa_postal_prin
cipal=1304 AND
BILHETAGEM-# dt_transacao >= to_date('01042005000
000', 'ddmmyyyyHH24MISS')
AND
BILHETAGEM-# dt_transacao <= to_date('01042005235
959', 'ddmmyyyyHH24MISS');

sum
-----

(1 row)

Look the rows number, all the information is on 01/04/2005, but only the
first sql can get the correct results.

I'm brazilian sorry for my bad english.

[]'s

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom Lane

2005-05-06, 8:24 pm

"Guilherme" <guilherme.silva@nexxera.com> writes:
> BILHETAGEM=# select sum(tm_arquivo) from bilhete where
> cd_caixa_postal_prin
cipal=1304 AND
> BILHETAGEM-# dt_transacao >= to_date('01042005000
000', 'ddmmyyyyHH24MISS')
> AND
> BILHETAGEM-# dt_transacao <= to_date('01042005235
959', 'ddmmyyyyHH24MISS');

to_date() produces a date, not a timestamp. I suspect you are after
to_timestamp. If dt_transacao is a timestamp, the above will only
select rows that are at exactly midnight of 2005-04-01, because that's
what the date values will promote to.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Tom Lane

2005-05-06, 8:24 pm

Guilherme Silva <guilherme.silva@nexxera.com> writes:
> Negative, i know what the to_date do.

I don't think you do, because you are feeding it input that it will
ignore --- the hh/mm/ss fields are just being thrown away.

regression=# select to_date('01042005000
000', 'ddmmyyyyHH24MISS');

to_date
------------
2005-04-01
(1 row)

regression=# select to_date('01042005235
959', 'ddmmyyyyHH24MISS');

to_date
------------
2005-04-01
(1 row)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Guilherme Silva

2005-05-07, 3:24 am

I understand you, sorry my english is too bad, ignore the last email.

I will try use to_timestamp.

Thanks.

Guilherme Silva
Programador - Planejamento
E-mail: guilherme.silva@nexxera.com
Tel/Fax: +55 48 2106-5698
ICQ: #119540871
http://www.nexxera.com
NEXXERA Tecnologia e Serviços S.A.
"Seu ambiente eletrônico de negócios"

Tom Lane wrote:

>"Guilherme" <guilherme.silva@nexxera.com> writes:
>
>
>
>to_date() produces a date, not a timestamp. I suspect you are after
>to_timestamp. If dt_transacao is a timestamp, the above will only
>select rows that are at exactly midnight of 2005-04-01, because that's
>what the date values will promote to.
>
> regards, tom lane
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Guilherme Silva

2005-05-07, 3:24 am

Negative, i know what the to_date do.

See the "dt_transacao _/*<=*/_ to_date("

The <= make the first sql equal to second

Second: "dt_transacao _/*<*/_ to_date('02042005000
000')"

Guilherme Silva
Programador - Planejamento
E-mail: guilherme.silva@nexxera.com
Tel/Fax: +55 48 2106-5698
ICQ: #119540871
http://www.nexxera.com
NEXXERA Tecnologia e Serviços S.A.
"Seu ambiente eletrônico de negócios"

Tom Lane wrote:

>"Guilherme" <guilherme.silva@nexxera.com> writes:
>
>
>
>to_date() produces a date, not a timestamp. I suspect you are after
>to_timestamp. If dt_transacao is a timestamp, the above will only
>select rows that are at exactly midnight of 2005-04-01, because that's
>what the date values will promote to.
>
> regards, tom lane
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Sponsored Links