CODEWITHSUNDEEP
mysql
hypocrite420
hypocrite420

Reputation: 11

calculating total time using timestamp

in my table i have fields (named id, binary and timestamp) getting data from a sensor set over a door, when the door is opened it send binary value as 1 with timestamp and its own id. when it's closed it sends binary value as 0 as well timestamp and id. and yes, it sends continuous data, not like when its opened just send data and stop, rather it keep going sending data with same value for binary and id except timestamp. for example yesterday when the door was opened for 2 seconds it sent data like this:

------------------------
id---binary---timestamp
------------------------
XX-----1------2014-12-29 21:09:08
XX-----1------2014-12-29 21:09:08
XX-----1------2014-12-29 21:09:08
XX-----1------2014-12-29 21:09:08
XX-----1------2014-12-29 21:09:08
XX-----1------2014-12-29 21:09:09
XX-----1------2014-12-29 21:09:09
XX-----1------2014-12-29 21:09:09
XX-----1------2014-12-29 21:09:09
XX-----1------2014-12-29 21:09:09
XX-----0------2014-12-29 21:09:10
XX-----0------2014-12-29 21:09:10
XX-----0------2014-12-29 21:09:10
........................................so on..

above picture can be found for whole day as many times the door opened with 1 value and rest of the time 0 value.

now, i want to calculate the total time duration to show in a graph everyday how long the door was open. But the problem is, i dont know from above data how to calculate time duration using timestamp. I know I can calculate using TIMEDIFF() and the TIME_TO_SEC() functions, but thats only when I have a start-time and ending time- in my case, I dont have.

I also thought an idea to select all value where binary=1 and sum them together, but thats not the real time duration, cause my sensor sometime sends same data 5times/sec, sometime sends 3time/sec. If it was fixed that always sending 5data/sec then may i sum up total and divide by 5 to find single second, but its not like that.

Anyone have idea to solve that?

Thanks in advance..:)

Upvotes: 0

Views: 1129

Answers (2)

fancyPants
fancyPants

Reputation: 51878

Okay, there are multiple possible answers.

If you are sure, that you have an entry for every second in your table, you can simply do this:

select
id, `binary`,
sec_to_time(count(distinct `timestamp`))
from
t
where `timestamp` between concat(curdate(), ' 00:00:00') and concat(curdate(), ' 23:59:59')
/*you could also do 
  where date(timestamp) = curdate()
but having a function on a column does not allow MySQL to use an index*/
group by id, `binary`

Please also note, that it's not a good idea to use reserved keywords as column names, like it's the case here with binary and timestamp. Not only are they scarcely descriptive, but you also always have the trouble to have to use backticks

With distinct you get only unique entries in that column. With count you count the seconds and with sec_to_time you transform it into a better readable format.

You can pimp this by putting an unique index over the columns (id, binary, timestamp) and instead of insert ... do insert ignore .... This way you would get only one entry per second in the table.

If you can't assume, that you have an entry for every second, then it gets more complicated. Best would be have an additional column, that indicates a value change in column binary. You can simulate it with a variable like in the following example, but it might not have good performance. 

SELECT `binary`, SEC_TO_TIME(SUM(secondsOnOffPerGroup))
FROM (
    SELECT
    id, `binary`, valueChangeGroup, TIMESTAMPDIFF(SECOND, MIN(`timestamp`), MAX(`timestamp`)) + 1 as secondsOnOffPerGroup
    FROM (
        SELECT
        t.*,
        @valueChangeGroup := IF(@prevB != `binary`, @valueChangeGroup + 1, @valueChangeGroup) as valueChangeGroup,
        @prevB := `binary`
        FROM
        t
        , (SELECT @prevB := null, @valueChangeGroup := 0) var_init_subquery_alias
        WHERE 
        `timestamp` between concat(curdate(), ' 00:00:00') and concat(curdate(), ' 23:59:59')
        ORDER BY id, `timestamp`
    ) sq
    GROUP BY id, `binary`, valueChangeGroup
) sq2
GROUP BY `binary`

What we do here, is to order by id and timestamp first. Then we increment a variable if the value of the current row differs from the previous row. In the outer query we group by this variable and get the min and max timestamp for each group, so we can calculate the difference. I'm adding + 1 there, because when the door is open or closed for just one second, the difference is obviously 0. If it's open or closed 2 seconds, the difference is 1. In the outermost query we group by binary and sum the seconds.

Upvotes: 1

Mirza
Mirza

Reputation: 369

If you want to check for what duration the door was opened each day then why you said that you dont have start and end time ?

You have boundary conditions as for example :

Start timestamp - 2014-12-29 00:00:01
End timestamp   - 2014-12-29 00:00:00

Now approach like this:

1) Set count=0 and totalOpenTime=0
2) if the row fetched have binary '1' set count = count + 1 then fetch next row
3) if in next row binary is '0' then add count to totalOpenTime and set count to 0
   but if in next row binary is '1' then set count = count + 1 and fetch next row
4) do this until timestamp < End timestamp

Hope it helps :)

Upvotes: 1

Related Questions