Detect when data changed

Question

Here is my data structure :

name          value          date_received
foo           100            2013-09-19 10:00:00
bar           200            2013-09-19 10:00:00
foo           100            2013-09-19 10:05:00  //no change
bar           200            2013-09-19 10:05:00  //no change
foo           110            2013-09-19 10:08:00  // foo changed          
bar           200            2013-09-19 10:08:00  // no change
......

Question:
I want a query (mysql) which can do something like:

select date_received where anyOf(foo, bar) changed from the previous 
specified value in the past N hours.

There could be other names in the table but we are only interested in foo and bar.

Any pointers. To me it looks like we'll need a self join - but don't know how.

EDIT: looks like the below query is just a good starting point.

select date_received from (SELECT DISTINCT name, value from data) a 
  INNER JOIN (select DISTINCT name, value, date_received from data)b 
    on (a.name=b.name and a.value=b.value)

update Looks like below query works - easier than I thought it would be.

SELECT DISTINCT a.tr FROM (
   SELECT name, value, MAX(date_received) dr from data 
    where date_received > now() - INTERVAL 2 hour 
    GROUP BY name, value order by dr desc)a;

Would it not be easier to only insert into the table when the data changes? — Rowland Shaw, Sep 20 '13 at 11:19
This is a feed from external system and some one decided to dump it in a table. But you are right, that would have been better. — karephul, Sep 20 '13 at 11:22
May be this will help you SELECT A.Name, A.Value FROM tbl A INNER JOIN tbl B ON A.Value = B.Value AND A.[date_received] < B.[date_received] WHERE A.date_received <> B.date_received — Hisham, Sep 20 '13 at 11:52
http://stackoverflow.com/questions/13708176/how-to-compare-two-rows-in-sql — Hisham, Sep 20 '13 at 11:53

score 1 · Answer 1 · answered Sep 20 '13 at 11:59

I do not see how your edited query solves the problem. Where does the "last N hours" come in, for instance?

I would approach this by looking at the previous value, then using logic around the datetime constraints and value changes to see if there has been a change. Your question is ambiguous: Are you looking for changes only in the last N hours? Are you looking for a change from the last value before N hours? What happens if the value changes back?

All of these, though, could be answered by having the previous value and previous time on each row. Here is an example of how to get this:

select t.*,
       (select t.date_received
        from t t2
        where t2.date_received < t.date_received and
              t2.name = t.name
        order by t2.date_received desc
        limit 1
       ) as prev_date_received,
       (select t.value
        from t t2
        where t2.date_received < t.date_received and
              t2.name = t.name
        order by t2.date_received desc
        limit 1
       ) as prev_value
from t
having <your logic goes here for the date time and changes you care about>;

This is using the having clause instead of a subquery, just out of convenience (this is not supported by other databases).

For instance, if you want any changes in the last N hours:

having date_received > now() - interval N hour and prev_value <> value

Gordon - I understand what you are trying to do logically, but looks like pref_value is coming out to be equal to value. Therefore, it returns me an empty set. — karephul, Sep 20 '13 at 12:34

Detect when data changed

1 Answers1