PostgreSQL Bugs - BUG #2093: SUM of timeintervals, no problems in 8.0.3

Author

BUG #2093: SUM of timeintervals, no problems in 8.0.3

Björn Sjölenius

2005-12-05, 7:24 am

The following bug has been logged online:

Bug reference: 2093
Logged by: Björn Sjölenius
Email address: salvium@gmail.com
PostgreSQL version: 8.1
Operating system: OpenBSD 3.8
Description: SUM of timeintervals, no problems in 8.0.3
Details:

Just upgraded fron 8.0.5 to 8.1, and a function where I summerize logon time
now give quite odd result. Duplicated the problem;

----------------- Commands to execute ------------------
createdb test
psql test

CREATE TABLE activity_tab (
nick character varying(32),
logon timestamp with time zone,
logoff timestamp with time zone
);

COPY activity_tab (nick, logon, logoff) FROM stdin USING DELIMITERS '|';
apa|2005-11-01 00:00:00+01|2005-11-01 23:00:00+01
apa|2005-11-02 00:00:00+01|2005-11-02 23:00:00+01
apa|2005-11-03 00:00:00+01|2005-11-04 23:00:00+01
\.

SELECT LOWER(nick) AS "nick", SUM(logoff-logon) AS "time"
FROM activity_tab
GROUP BY LOWER(nick);

\q

dropdb test

----------------- End of commands ------------------

The result I get;
nick | time
------+----------------
apa | 1 day 69:00:00

Expected result is what 8.0.3 give me;
nick | time
------+----------------
apa | 3 days 21:00:00

Hope you can help! And a BIG THANKYOU for your great work!

Best regards, Björn

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Alvaro Herrera

2005-12-05, 9:24 am

Björn Sjölenius wrote:

> The result I get;
> nick | time
> ------+----------------
> apa | 1 day 69:00:00
>
>
> Expected result is what 8.0.3 give me;
> nick | time
> ------+----------------
> apa | 3 days 21:00:00

Actually this is expected. 8.1 groups hours and days separately,
because there is no way to know if 1 day means 23, 24 or 25 hours (or
even a fractional number like 24.5) in the presence of a daylight-saving
switch event. So "1 day 69 hours" is actually more correct.

There is a function to convert to the older format:

alvherre=# select justify_hours('1 day 69:00:00');
justify_hours
-----------------
3 days 21:00:00
(1 fila)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links