Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    WA, USA

    Question Unanswered: Extraction of records at specific time interval

    I have a large volume of data that was recorded every second.
    I want to extract data from the database every "x" number of seconds.
    i.e. SELECT * FROM data WHERE time = (every 30 seconds);

    The goal is to reduce the data set down to a number of records over time at an interval determined by the user.

    What is your recommendation on how to accomplish this task?

    I have in mind a javascript dialog asking the user for the time interval, this passes the value to a PHP program that loops the SQL query, incrementing the time='x' value each time by the interval value and writing the results to screen/file.

    Any ideas or ways to do this directly in SQL?

    Thank you,

  2. #2
    Join Date
    Sep 2003
    how about..

    SELECT * FROM data WHERE time % 30 = 0

    I'm not sure if that would actually work in mysql , but that expression is the simplest way to explain it

    the other option is to do some calculations (with php I assume) before the data is inserted, and determine if the time fits any time patterns. You could have columns to show if the time matches a 2 second, 3 second, 4 second, 5 second, 7 second or 9 second interval.

    So if $time % 2 = 0 , you insert a '1' for the 2second column, if it doesnt you insert a 0 , same for all other intervals.

    And then to get all data that matches the 30 second frequency...

    SELECT * FROM data WHERE 2second = 1 AND 3second = 1 AND 5second = 1

    2 * 3 * 5 = 30 , so any data matching those 3 columns must have a 30 second frequency.

    With those 6 values you could make most combinations for all the frequencys you want to show.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts