To: pgsql-sql@postgresql.org
Subject: Re: finding gaps in dates
References: <27c475ec04111612561a696fd1@mail.gmail.com>
Message-ID: <opshkulz0kcq72hf@musicbox>
Date: Tue, 16 Nov 2004 22:43:01 +0100
From: =?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?=
	<lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <27c475ec04111612561a696fd1@mail.gmail.com>
User-Agent: Opera M2/7.54 (Linux, build 751)


> 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.

	Is this not the same question ?

	Answer to a:

	If your script is set to run at every minute + 00 seconds, if it ever  
runs one second earlier, timestamp-truncate will keep the previous minute  
and you're screwed. A simple solution would be to have your script run  
every minute + 30 seconds.

	Answer to b:

	If you can do the following : examine the records in chronological order,  
every time computing the delay between record N and record N-1 ; if this  
delay is not one minute +/- a few seconds, you have detected an anomaly.
	Problem : you need to scan the whole table for anomalies every time.
	Solution : put an ON INSERT trigger on your log table which :
	- checks the current time for sanity (ie. is it +/- a few seconds from  
the expected time ?)
	This solves part of a)
	- looks at the timestamp of the latest row, computes the difference with  
the inserted one, and if > than 1 minute + a few seconds, inserts a row in  
an anomaly logging table.
	This solves the rest of a) and b)

	It's just an additional SELECT x FROM table ORDER BY timestamp DESC LIMIT  
1 which has a negligible performance impact compared to your insert.