pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddate_trunc for 5 minutes intervals
4+ messages / 3 participants
[nested] [flat]
* date_trunc for 5 minutes intervals
@ 2003-10-19 13:38 email lists <lists@darrenmackay.com>
2003-10-19 21:00 ` Re: date_trunc for 5 minutes intervals Mathieu Arnold <mat@mat.cc>
2003-10-20 08:27 ` Re: date_trunc for 5 minutes intervals Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 4+ messages in thread
From: email lists @ 2003-10-19 13:38 UTC (permalink / raw)
To: pgsql-sql
Hi All,
I am wanting to perform the equivalent of date_trunc to 5/10/15 minute
intervals. As this does mnot seem to be natively supported by
date_trunc, can anyone point me in the right direction to possible write
the SQL ro acheive the desired outcome?
Tia,
Darren
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: date_trunc for 5 minutes intervals
2003-10-19 13:38 date_trunc for 5 minutes intervals email lists <lists@darrenmackay.com>
@ 2003-10-19 21:00 ` Mathieu Arnold <mat@mat.cc>
1 sibling, 0 replies; 4+ messages in thread
From: Mathieu Arnold @ 2003-10-19 21:00 UTC (permalink / raw)
To: email lists <lists@darrenmackay.com>; pgsql-sql
+-Le 19/10/2003 23:38 +1000, email lists écrivait :
| Hi All,
|
| I am wanting to perform the equivalent of date_trunc to 5/10/15 minute
| intervals. As this does mnot seem to be natively supported by
| date_trunc, can anyone point me in the right direction to possible write
| the SQL ro achieve the desired outcome?
maybe (date_part(epoch, field)::integer / 300 * 300)::timetamp for 5
minutes interval...
--
Mathieu Arnold
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: date_trunc for 5 minutes intervals
2003-10-19 13:38 date_trunc for 5 minutes intervals email lists <lists@darrenmackay.com>
@ 2003-10-20 08:27 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2003-10-20 08:27 UTC (permalink / raw)
To: email lists <lists@darrenmackay.com>; pgsql-sql
On Sunday 19 October 2003 14:38, email lists wrote:
> Hi All,
>
> I am wanting to perform the equivalent of date_trunc to 5/10/15 minute
> intervals. As this does mnot seem to be natively supported by
> date_trunc, can anyone point me in the right direction to possible write
> the SQL ro acheive the desired outcome?
I'd probably convert to seconds-since-epoch and then subtract that value
modulo 300 and convert back.
Might be worth checking the cookbook link on http://techdocs.postgresql.org/
since I can't believe you are the first person to need this.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: date_trunc for 5 minutes intervals
@ 2003-10-20 13:04 email lists <lists@darrenmackay.com>
0 siblings, 0 replies; 4+ messages in thread
From: email lists @ 2003-10-20 13:04 UTC (permalink / raw)
To: pgsql-sql
Hi,
Thanks for the several replies both on and off the list.
To be more specific, I am wanting to aggregate data to a 5/10/15 min
interval. Currently, I am aggregating data that falls in hour / day /
month / year periods for both count() and sum(). The sql I am currently
using is:
SELECT count(id) AS count, sum(conn_bytes) AS
sum, hisec_port, conn_protocol,
date_trunc('hour'::text, datetime) AS date_trunc
FROM trafficlogs
WHERE (conn_outbound = false)
GROUP BY date_trunc('hour'::text, datetime),
conn_protocol, hisec_port
HAVING (count(*) = ANY (
SELECT count(*) AS count
FROM trafficlogs
GROUP BY hisec_port, date_trunc('hour'::text, datetime)
ORDER BY count(*) DESC)
);
Which produces:
count sum hisec_portconn_protocol date_trunc
12 192 5,050 2003/09/17 00:00:00
11 176 5,050 2003/09/17 01:00:00
12 192 5,050 2003/09/17 02:00:00
11 176 5,050 2003/09/17 03:00:00
10 160 5,050 2003/09/17 04:00:00
- if you know of a more efficient way to do this than the sql above, pls
let me know
In my previous post I should have said I wanted to aggregating data in
5/10/15 min intervals in a similar manner to the above
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-10-20 13:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-19 13:38 date_trunc for 5 minutes intervals email lists <lists@darrenmackay.com>
2003-10-19 21:00 ` Mathieu Arnold <mat@mat.cc>
2003-10-20 08:27 ` Richard Huxton <dev@archonet.com>
2003-10-20 13:04 Re: date_trunc for 5 minutes intervals email lists <lists@darrenmackay.com>
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