Received: from pegasus.freibergnet.de (pegasus.freibergnet.de [194.123.255.9]) by hub.org (8.9.3/8.9.3) with ESMTP id EAA62057 for ; Fri, 29 Oct 1999 04:14:32 -0400 (EDT) (envelope-from holm@pegasus.freibergnet.de) Received: (from holm@localhost) by pegasus.freibergnet.de (8.9.3/8.9.1) id KAA65402 for pgsql-sql@postgresql.org; Fri, 29 Oct 1999 10:14:30 +0200 (CEST) (envelope-from holm) Date: Fri, 29 Oct 1999 10:14:30 +0200 From: Holm Tiffe To: pgsql-sql@postgresql.org Subject: Problem with datetime Message-ID: <19991029101430.B65318@pegasus.freibergnet.de> Reply-To: holm@freibergnet.de Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Mailer: Mutt 0.95i Organization: FreibergNet Internet Services Priority: normal X-Phone: +49-3731-781279 X-Fax: +49-3731-781377 X-PGP-fingerprint: 86 EC A5 63 B5 28 78 13 8B FC E9 09 04 6E 86 FC Hi, I have a table on pGSQL 6.5.2 wich contains several accounting data from radiusd in the following form: bwin int8 bwout int8 name varchar() start datetime stop datetime callerid varchar() fr_protocol int4 fr_addr inet data_rate int4 disconn_cause int4 I'm now trying to get summaries of the data in several time intervals eg: SELECT sum(stop-start) FROM logs WHERE name = 'intern-mw' and start >= '28-10-1999' AND stop < ('28-10-1999'::datetime + '1 day'::timespan); sum ------------------------- @ 2 hours 57 mins 56 secs (1 row) Thats ok The same for the 10-27-1999: SELECT sum(stop-start) FROM logs WHERE name = 'intern-mw' and start >= '27-10-1999' AND stop < ('27-10-1999'::datetime + '1 day'::timespan); sum ------------------------- @ 4 hours 37 mins 20 secs (1 row) I think, thats also ok. But now both days at on time: SELECT sum(stop-start) FROM logs WHERE name = 'intern-mw' and start >= '27-10-1999' AND stop < ('28-10-1999'::datetime + '1 day'::timespan); sum ------------------------- @ 12 hours 51 mins 4 secs (1 row) uups what's happening here ? I've doublechecked the WHERE clause, It is ok, the right tuples are selected, but 3h + 4.5h are not 13h! Wat I'm doing wrong here ? It is possible to get the timespan in minutes only ? I mean the entire timespan in minutes not the minutepart of the timespan. Thanks, Holm -- FreibergNet Systemhaus GbR Holm Tiffe * Administration, Development Systemhaus für Daten- und Netzwerktechnik phone +49 3731 781279 Unternehmensgruppe Liebscher & Partner fax +49 3731 781377 D-09599 Freiberg * Am St. Niclas Schacht 13 http://www.freibergnet.de/