Daniel - 1 year ago 58
MySQL Question

# R - calculating time difference

I have a question about calculating the time difference for different sources and targets.

``````Table1:

Source      Target     Time                   TimeDif(wrong) (right)
1.2.3.4     2.3.4.5    2012-01-03 21:50:40    3               3
1.2.3.4     2.3.4.5    2014-01-03 21:50:43    5               5
1.2.3.4     2.3.4.5    2014-01-03 21:50:48    3               NULL
2.2.2.2     4.4.4.4    2014-01-03 21:50:51    3               4
2.2.2.2     4.4.4.4    2014-01-03 21:50:55    4               4
2.2.2.2     4.4.4.4    2014-01-03 21:50:59    4               NULL
....        ....       ......
``````

Right now I calculate the time difference with:

``````diffTime <- difftime(time[1:(length(time)-1)] , time[2:length(time)]) * -1
``````

The problem is that the time difference by calculating this method are not correct. That means the method calculate the whole column and does not make any decision between different sources and targets. I'm not sure if this problem can be fixed by the package (sqldf) to query and to group the data together. The query should also implement the method, but I think that is not possible. So would be nice if you have any solutions.

Supposing you want to do stuff in R, you need a grouping function. With for example `group_by` from `dplyr` you can do that:

``````library(dplyr)
dat %>%
group_by(Source, Target) %>%
``````

the result:

``````   Source  Target                Time          tdif
<fctr>  <fctr>              <dttm>        <time>
1 1.2.3.4 2.3.4.5 2012-01-03 21:50:40 63158403 secs
2 1.2.3.4 2.3.4.5 2014-01-03 21:50:43        5 secs
3 1.2.3.4 2.3.4.5 2014-01-03 21:50:48       NA secs
4 2.2.2.2 4.4.4.4 2014-01-03 21:50:51        4 secs
5 2.2.2.2 4.4.4.4 2014-01-03 21:50:55        4 secs
6 2.2.2.2 4.4.4.4 2014-01-03 21:50:59       NA secs
``````

Note that the first `tdiff` value is much larger than what you stated in the intended output. This is richt and due to the fact that the date of the first timestamp is from 2012, while the other timestamps are from 2014.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download