pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Holm Tiffe <holm@freibergnet.de>
To: pgsql-sql@postgresql.org
Subject: Problem with datetime
Date: Fri, 29 Oct 1999 10:14:30 +0200
Message-ID: <19991029101430.B65318@pegasus.freibergnet.de> (raw)

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/




view thread (2+ messages)  latest in thread

Message-ID: <19991029101430.B65318@pegasus.freibergnet.de>
Permalink:  ../19991029101430.B65318@pegasus.freibergnet.de/
Also on:    postgresql.org/message-id/19991029101430.B65318@pegasus.freibergnet.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: holm@freibergnet.de
  Subject: Re: Problem with datetime
  In-Reply-To: <19991029101430.B65318@pegasus.freibergnet.de>
  References: <19991029101430.B65318@pegasus.freibergnet.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox