CODEWITHSUNDEEP
mysqlperldatetimesumdatediff
eLemEnt
eLemEnt

Reputation: 1801

Mysql add time based on conditions

I have one table whose structure is like as follows

 +---------------------+---------+
| date_time           | portNo1 |
+---------------------+---------+
| 2016-01-08 13:15:46 |       1 | 
| 2016-01-08 13:16:01 |       2 | 
| 2016-01-08 13:16:16 |       1 | 
| 2016-01-08 13:16:31 |       2 | 
| 2016-01-08 13:16:46 |       1 | 
| 2016-01-08 13:17:00 |       2 | 
| 2016-01-08 13:17:16 |       1 | 
| 2016-01-08 13:17:31 |       1 | 
| 2016-01-08 13:17:46 |       0 | 
| 2016-01-08 13:18:01 |       0 | 
| 2016-01-08 13:18:16 |       0 | 
| 2016-01-08 13:18:31 |       0 | 
| 2016-01-08 13:18:41 |       0 | 
| 2016-01-08 13:19:01 |       0 | 
| 2016-01-08 13:19:16 |       0 | 
| 2016-01-08 13:19:27 |       0 | 
| 2016-01-08 13:19:41 |       0 | 
| 2016-01-08 13:19:56 |       0 | 
| 2016-01-08 13:20:11 |       0 | 
| 2016-01-08 13:20:26 |       0 | 
| 2016-01-08 13:20:41 |       0 | 
| 2016-01-08 13:20:56 |       0 | 
| 2016-01-08 13:21:16 |       0 | 
| 2016-01-08 13:21:31 |       0 | 
| 2016-01-08 13:21:43 |       0 | 
| 2016-01-08 13:22:01 |       0 | 
| 2016-01-08 13:22:16 |       0 | 
| 2016-01-08 13:22:31 |       0 | 
| 2016-01-08 13:22:46 |       0 | 
| 2016-01-08 13:23:01 |       0 | 
| 2016-01-08 13:23:12 |       0 | 
| 2016-01-08 13:23:31 |       0 | 
| 2016-01-08 13:23:46 |       2 | 
| 2016-01-08 13:24:01 |       2 | 
| 2016-01-08 13:24:16 |       2 | 
| 2016-01-08 13:24:31 |       1 | 
| 2016-01-08 13:24:46 |       2 | 
| 2016-01-08 13:25:00 |       2 |

Now what I have to do is only add the time whose portNo1 value is not 0 that means it should add time from 13:15:46 to 13:17:31 in seconds then since after that all record till 13:23:46 value is 0 it should ignore that time and continue. then again from 13:23:46 has portNo1 value it should take difference between 13:23:46 and 13:23:31 and add it to the previous difference. Guys I tried lot in perl to do it but not getting correct values if there is any easy way in mysql please tell me.

Upvotes: 0

Views: 166

Answers (1)

Jcl
Jcl

Reputation: 28272

Unless I'm understanding the question all wrong, you can do something like:

SELECT SUM(UNIX_TIMESTAMP(date_time)-UNIX_TIMESTAMP(prevtime))
FROM (
   SELECT date_time, portNo1, @prevTime as prevtime,
     @prevTime := date_time
   FROM MyTable
) t1 
WHERE (portNo1 > 0 AND prevtime IS NOT null)

This will sum the time differences (in seconds) between the current row and the previous row if the portNo1 in the current row is greater than 0. Is this what you want?

I've made a fiddle here: http://sqlfiddle.com/#!9/b6dfa/3

Upvotes: 1

Related Questions