From: | Matt Nuzum <matt(dot)followers(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | finding gaps in dates |
Date: | 2004-11-16 20:56:31 |
Message-ID: | 27c475ec04111612561a696fd1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I have a logging application that should produce an entry in the
database every minute or so, give or take a few seconds.
I'm interested in finding out
a: what minutes don't have a record and
b: periods where the gap exceeded a certain amount of time.
The only way I can think of to do it is to create a set returning
function that accepts a begin and end date and returns a
date_trunc('minute', ...) for each minute between then and then doing
Select ts from srf(now() - '30 days'::inteval, now()) where ts not in
(select distinct date_trunc('minute', ts) from table where ts between
now() - '30 days'::inteval and now());
Of course, that seems extremely tedious to me (not tedious to code,
but tedious for the db to process).
If anyone can suggest a better way I'd really appreciate it.
--
Matthew Nuzum | Makers of "Elite Content Management System"
www.followers.net | View samples of Elite CMS in action
matt(at)followers(dot)net | http://www.followers.net/portfolio/
From | Date | Subject | |
---|---|---|---|
Next Message | Pierre-Frédéric Caillaud | 2004-11-16 21:43:01 | Re: finding gaps in dates |
Previous Message | Thomas F.O'Connell | 2004-11-16 20:09:33 | Re: Counting Distinct Records |