pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with datetime
2+ messages / 2 participants
[nested] [flat]
* Problem with datetime
@ 1999-10-29 08:14 Holm Tiffe <holm@freibergnet.de>
0 siblings, 1 reply; 2+ messages in thread
From: Holm Tiffe @ 1999-10-29 08:14 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] Problem with datetime
@ 1999-10-29 13:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Holm Tiffe <holm@freibergnet.de>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 1999-10-29 13:44 UTC (permalink / raw)
To: holm@freibergnet.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-10-29 13:44 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-29 08:14 Problem with datetime Holm Tiffe <holm@freibergnet.de>
1999-10-29 13:44 ` Tom Lane <tgl@sss.pgh.pa.us>
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