In the last episode (Apr 29), David Lindelf said:
> Is there an easy way to get the last timestamped record not later
> than a given date/time?
>
> I record data for a scientific application and I do not sample my
> data at a given, fixed frequency. Instead, whenever a physical value
> changes beyond a given threshold, I get a new timestamped value which
> is stored in the database.
>
> For data analysis however it is more convenient to have a
> regularly-spaced timeseries of that data. For instance, if I get
> temperature measurements at 7:56, 8:02, 8:13 and 8:27, and would like
> a timeseries 15-minutes apart (8:00, 8:15, 8:30), I would like MySQL
> to automatically figure out that I need the measurements from 7:56,
> 8:13 and 8:27.
You could probably do it with a bunch of selects, each fetching the row
before each time period, all UNIONed together.
It might be better to just fetch the timestamps for all the records
within your time range of interest (which should be fast assuming
you've got an index on your time field), figure out which ones you want
in whatever language you're most familiar with, then fetch the records
with another single statement "... WHERE timestampfield IN ('time1',
'time2', 'time3')". That would also let you get a bit fancier: For
example, select the sample nearest to your 15-minute marks, rather than
the one less than the mark. Or maybe select the sample nearest to the
point 15 minutes after the previous sample, which will end up with you
drifting off the "quarter of the hour" marks but will get you better
spacing between samples.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
