[thelist] SQL - nearest in time

Means, Eric D eric.d.means at boeing.com
Wed Apr 24 17:12:01 CDT 2002


>  -----Original Message-----
> From: 	David Shadovitz [mailto:david_shadovitz at xontech.com]
> Sent:	Wednesday, April 24, 2002 5:01 PM
> To:	thelist at lists.evolt.org
> Subject:	[thelist] SQL - nearest in time
>
> I've got a database table containing measurements taken every few
> seconds, non-periodically:
> Measurements {PK ExperimentID, TimeInSeconds, Item1, Item2, Item3}
...
> But the difficulty is that here won't be an exact match between
> TimeInSeconds and the input time.  I'll settle for the nearest record,
> or the nearest record before the input time, or nearest record after the
> input time.

One way to select the nearest record before or the nearest record after (but
not both) would be (pseudo-code ahead)
SELECT *
FROM Measurements
WHERE TimeInSeconds = (SELECT min(TimeInSeconds) FROM
	(SELECT TimeInSeconds FROM Measurements WHERE TimeInSeconds >
:InputTime))

For the nearest time before change min() to max() and > to <.

If your DB won't allow subselects in the FROM clause, select the times into
a temp table and then select the min or max from there instead.



More information about the thelist mailing list