DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding;
	b=B3vSGRvDbM5v0KULXhTDPGTXRBjEqMU5n5qZeXPoUDyl88vEQn0ER5M/w5JlZhVbFjgodYh9+rkuiqTi3I4cL0ERcfZK7rV52rHGEhvlLoX5XRXzxUfYqVEKsHnzhNiP2+dGzxs+8cSyxanO+LA4zjX4OFrm589sUmIuEWqbgoA=
Message-ID: <27c475ec04111612561a696fd1@mail.gmail.com>
Date: Tue, 16 Nov 2004 15:56:31 -0500
From: Matt Nuzum <matt.followers@gmail.com>
Reply-To: matt@followers.net
To: pgsql-sql@postgresql.org
Subject: finding gaps in dates
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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/