pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfinding gaps in dates
2+ messages / 2 participants
[nested] [flat]
* finding gaps in dates
@ 2004-11-16 20:56 Matt Nuzum <matt.followers@gmail.com>
2004-11-16 21:43 ` Re: finding gaps in dates Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
0 siblings, 1 reply; 2+ messages in thread
From: Matt Nuzum @ 2004-11-16 20:56 UTC (permalink / raw)
To: 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@followers.net | http://www.followers.net/portfolio/
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: finding gaps in dates
2004-11-16 20:56 finding gaps in dates Matt Nuzum <matt.followers@gmail.com>
@ 2004-11-16 21:43 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
0 siblings, 0 replies; 2+ messages in thread
From: Pierre-Frédéric Caillaud @ 2004-11-16 21:43 UTC (permalink / raw)
To: 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.
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-11-16 21:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-16 20:56 finding gaps in dates Matt Nuzum <matt.followers@gmail.com>
2004-11-16 21:43 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
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