CODEWITHSUNDEEP
mysqlsql
Nimit Vachhani
Nimit Vachhani

Reputation: 65

Filter Rows with time difference

I have a "readings" table with columns

  1. timestamp as DateTime
  2. channel_id as integer
  3. value as Decimal
  4. test_id as integer

Now i want to filter rows by test_id which anybody can do. Then in the result number of rows is too much. Suppose if i use channel number 2,3 and 5 for my test_id 17 and if I log data for every second then there are 10k rows.

When I plot the graph there where will so many data that the graph lines are not visible clearly, so to make them visible I need to filter out some rows.

I need some help in filtering of rows with time difference between the two records should be few seconds lets say 10 seconds. In this the data is not consecutive.

Any help will be greatly appreciated.

The sample data will be as follows: 

24-05-2016 08:00:55 am | 2 | 10.23  | 17

24-05-2016 08:00:55 am | 3 | 100.23 | 17

24-05-2016 08:00:55 am | 5 | 12.23  | 17

24-05-2016 08:00:56 am | 2 | 09.23  | 17

24-05-2016 08:00:56 am | 3 | 12.23  | 17

24-05-2016 08:00:56 am | 5 | 11.23  | 17

24-05-2016 08:00:57 am | 2 | 09.23  | 17

24-05-2016 08:00:57 am | 3 | 01.23  | 17

24-05-2016 08:00:57 am | 5 | 11.23  | 17

24-05-2016 08:00:58 am | 2 | 09.23  | 17

24-05-2016 08:00:58 am | 3 | 01.23  | 17

24-05-2016 08:00:58 am | 5 | 11.23  | 17

Upvotes: 0

Views: 197

Answers (1)

amaksr
amaksr

Reputation: 7745

Instead of filtering you can aggregate data for the period using GROUP BY functionality. For example, 10-second periods can be calculated from the timestamps using this formula:

ROUND(UNIX_TIMESTAMP(timestamp)/10)

So, this formula can be added to GROUP BY query, so query can aggregate data on that period:

SELECT test_id,channel_id,ROUND(UNIX_TIMESTAMP(timestamp)/10),
    min(value), max(value), avg(value),count(*)
FROM your_table
WHERE some_conditions
GROPU BY test_id,channel_id,ROUND(UNIX_TIMESTAMP(timestamp)/10)

Upvotes: 1

Related Questions