Обсуждение: 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
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
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: > > >>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: >"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 > > >