From: Matt Nuzum <matt.followers@gmail.com>
To: pgsql-sql@postgresql.org
Subject: finding gaps in dates
Date: Tue, 16 Nov 2004 15:56:31 -0500
Message-ID: <27c475ec04111612561a696fd1@mail.gmail.com> (raw)
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@followers.net | http://www.followers.net/portfolio/
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: matt.followers@gmail.com
Subject: Re: finding gaps in dates
In-Reply-To: <27c475ec04111612561a696fd1@mail.gmail.com>
* 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