From: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
To: pgsql-sql@postgresql.org
Subject: Re: finding gaps in dates
Date: Tue, 16 Nov 2004 22:43:01 +0100
Message-ID: <opshkulz0kcq72hf@musicbox> (raw)
In-Reply-To: <27c475ec04111612561a696fd1@mail.gmail.com>
References: <27c475ec04111612561a696fd1@mail.gmail.com>
> 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.
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: lists@boutiquenumerique.com
Subject: Re: finding gaps in dates
In-Reply-To: <opshkulz0kcq72hf@musicbox>
* 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