Odd sum() problem in 7.2.2
I'm stuck. The following two snippets gives me two different results,
and I can't figure out why;
1) select abs(0 + int4(sum((int4(ss7_rel_timestamp_sec) - int4
(start_timestamp_sec)) / 60))) from session_billed where (b_nr
like '12345678%' and datetime(ss7_rel_timestamp_sec) >=
'2002-09-01' and datetime(ss7_rel_timestamp_sec) < '2002-10-01'
and session_status_reached >= 20);
= 5845507 and count(*) [1]select count(*) from session_billed where <rest of original query> gives me: 286109
2) select int4(sum(ss7_rel_timestamp_sec - start_timestamp_sec) / 60)
from session_billed where b_nr like '12345678%' and
ss7_rel_timestamp_sec >= '2002-09-01' and ss7_rel_timestamp_sec
< '2002-10-01' and session_status_reached >= 20;
= 5986420 and count(*) [1]select count(*) from session_billed where <rest of original query> gives me: 286304
ss7_rel_timestamp_sec = abstime
start_timestamp_sec = abstime
b_nr = character varying(32)
session_status_reached = integer
There have been no changes in the table between these two queries.
PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC 2.95.4.
[1]: select count(*) from session_billed where <rest of original query>
--
This is your life, and it's ending one minute at a time.
On Friday 04 Oct 2002 11:37 am, Andreas Forsgren wrote:
I'm stuck. The following two snippets gives me two different results,
and I can't figure out why;
= 5845507 and count(*) [1] gives me: 286109
= 5986420 and count(*) [1] gives me: 286304
Nothing changed but different numbers of rows returned? Best look at the where
clause...
where (b_nr
like '12345678%' and datetime(ss7_rel_timestamp_sec) >=
'2002-09-01' and datetime(ss7_rel_timestamp_sec) < '2002-10-01'
and session_status_reached >= 20);where b_nr like '12345678%' and
ss7_rel_timestamp_sec >= '2002-09-01' and ss7_rel_timestamp_sec
< '2002-10-01' and session_status_reached >= 20;
Hmm - "datetime" - tell me sir, are you in a timezone other than Greenwich and
if you specify timezones explicitly does the problem go away?
--
Richard Huxton
Richard Huxton <dev@archonet.com> writes:
Nothing changed but different numbers of rows returned? Best look at the where
clause...
Hmm - "datetime" - tell me sir, are you in a timezone other than Greenwich and
if you specify timezones explicitly does the problem go away?
It looks to me like 7.2 has a bug in
abstime-to-timestamp-without-time-zone conversion, which is what that
datetime() thing is invoking. In 7.2.3, running in US EST5EDT zone,
I see:
regression=# select abstime '2002-09-01';
abstime
------------------------
2002-09-01 00:00:00-04
(1 row)
regression=# select (abstime '2002-09-01')::timestamp without time zone;
timestamp
---------------------
2002-09-01 08:00:00
^^ !!!
(1 row)
regression=# select (abstime '2002-09-01')::timestamp with time zone;
timestamptz
------------------------
2002-09-01 00:00:00-04
(1 row)
In 7.3beta I get more sensible results:
regression=# select (abstime '2002-09-01')::timestamp without time zone;
timestamp
---------------------
2002-09-01 00:00:00
(1 row)
so I'd say Thomas fixed the bug while he was working on the datetime
types this summer ...
regards, tom lane