BUG #1653: Bug in date
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_principal=1304 AND
BILHETAGEM-# dt_transacao >= to_date('01042005000000', 'ddmmyyyyHH24MISS')
AND
BILHETAGEM-# dt_transacao < to_date('02042005000000', 'ddmmyyyyHH24MISS');
sum
-----
968
(1 row)
BILHETAGEM=# select sum(tm_arquivo) from bilhete where
cd_caixa_postal_principal=1304 AND
BILHETAGEM-# dt_transacao >= to_date('01042005000000', 'ddmmyyyyHH24MISS')
AND
BILHETAGEM-# dt_transacao <= to_date('01042005235959', '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
"Guilherme" <guilherme.silva@nexxera.com> writes:
BILHETAGEM=# select sum(tm_arquivo) from bilhete where
cd_caixa_postal_principal=1304 AND
BILHETAGEM-# dt_transacao >= to_date('01042005000000', 'ddmmyyyyHH24MISS')
AND
BILHETAGEM-# dt_transacao <= to_date('01042005235959', '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
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('02042005000000')"
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:
Show quoted text
"Guilherme" <guilherme.silva@nexxera.com> writes:
BILHETAGEM=# select sum(tm_arquivo) from bilhete where
cd_caixa_postal_principal=1304 AND
BILHETAGEM-# dt_transacao >= to_date('01042005000000', 'ddmmyyyyHH24MISS')
AND
BILHETAGEM-# dt_transacao <= to_date('01042005235959', '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
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:
Show quoted text
"Guilherme" <guilherme.silva@nexxera.com> writes:
BILHETAGEM=# select sum(tm_arquivo) from bilhete where
cd_caixa_postal_principal=1304 AND
BILHETAGEM-# dt_transacao >= to_date('01042005000000', 'ddmmyyyyHH24MISS')
AND
BILHETAGEM-# dt_transacao <= to_date('01042005235959', '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
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('01042005000000', 'ddmmyyyyHH24MISS');
to_date
------------
2005-04-01
(1 row)
regression=# select to_date('01042005235959', 'ddmmyyyyHH24MISS');
to_date
------------
2005-04-01
(1 row)
regards, tom lane