0

I have a column ("timestamp2") including 66 times and want to find the nearest time for each time, and then calculate the time interval for these two times.

enter image description here

For example, the first time in the column "timestamp2" is "2020-08-18 18:15:37", the nearest time to this time should be "2020-08-18 18:15:44" and the time interval between these two times should be 6 seconds.

There are many solutions to the problem of finding the closest time for a given time in a time column. My question is a little different as it requires searching within the given column and needs to require the time and time interval.

Zhenyu
  • 35
  • 7
  • 1
    hello, can you use `dput` on a sample of your data to make this [a reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example)? – bouncyball Sep 02 '20 at 12:56
  • 1
    please provide your data in a reusable format. – Humpelstielzchen Sep 02 '20 at 12:57
  • Maybe something like: `x <- as.POSIXct(c("2020-08-18 18:15:37", "2020-08-18 18:15:44", "2020-08-18 18:15:50")); lapply(seq_along(x), function(i) min(abs(x[i] - x[-i])))` – GKi Sep 02 '20 at 13:05
  • do you need the answer for SQL (TSQL?) – RoKli Sep 02 '20 at 13:32

1 Answers1

2

You could e.g. use order and diff:

DF <- data.frame(i = 1:66, timestamp2 = Sys.time()-runif(66, min = 1, max = 100))
DF <- DF[order(DF$timestamp2),]
DF$timediff <- c(NA_real_, diff(DF$timestamp2))

    i          timestamp2     timediff
64 64 2020-09-02 15:11:47           NA
65 65 2020-09-02 15:11:48 1.1161799431
58 58 2020-09-02 15:11:52 3.4341783524
13 13 2020-09-02 15:11:53 1.0964329243
5   5 2020-09-02 15:11:55 2.0064251423
59 59 2020-09-02 15:11:55 0.0007073879

Or if you just want the resulting vector: diff(sort(DF$timestamp2))

ismirsehregal
  • 30,045
  • 5
  • 31
  • 78