Thread: Problem with datetime

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' andstart >= '28-10-1999' AND stop < ('28-10-1999'::datetime +
'1day'::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' andstart >= '27-10-1999' AND stop < ('27-10-1999'::datetime +
'1day'::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' andstart >= '27-10-1999' AND stop < ('28-10-1999'::datetime +
'1day'::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/

Holm Tiffe <holm@freibergnet.de> writes:
> 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 ?

Maybe I'm missing something obvious, but it seems to me that the above
command will include log entries that cross the midnight boundary
between the two dates, whereas neither of the other two do.  Seems like
you must have about 5.5h worth of such entries...
        regards, tom lane