Difference in Timestamp

Question

I want to calculate the difference of two incidents. First five columns indicate a date-time of incident. The rest five columns indicate the date-time of death.

dat <- read.table(header=TRUE, text="
YEAR MONTH DAY HOUR MINUTE D.YEAR D.MONTH D.DAY D.HOUR D.MINUTE
2013     1   6    0     55   2013       1     6      0       56
2013     2   3   21     24   2013       2     4     23       14
2013     1   6   11     45   2013       1     6     12       29
2013     3   6   12     25   2013       3     6     23       55
2013     4   6   18     28   2013       5     3     11       18
2013     4   8   14     31   2013       4     8     14       32")
dat
YEAR MONTH DAY HOUR MINUTE D.YEAR D.MONTH D.DAY D.HOUR D.MINUTE
2013     1   6    1     55   2013       1     6      0       56
2013     2   3   21     24   2013       2     4     23       14
2013     1   6   11     45   2013       1     6     12       29
2013     3   6   12     25   2013       3     6     23       55
2013     4   6   18     28   2013       5     3     11       18
2013     4   8   14     31   2013       4     8     14       32

I want to calculate the difference of time (in minutes). The following code is not going anywhere. The timestamp will look like 2013-04-06 04:08.

library(lubridate)
dat$tstamp1 <- mdy(paste(dat$YEAR, dat$MONTH, dat$DAY, dat$HOUR, dat$MINUTE,sep = "-"))

dat$tstamp2 <- mdy(paste(dat$D.YEAR, dat$D.MONTH, dat$D.DAY, dat$D.HOUR, dat$D.MINUTE, sep = "-"))

dat$diff <- dat$tstamp2 -dat$tstamp2 ### want the difference in minutes

How can you have both `0` and `24` as the hour under `D.HOUR`? — David Robinson, Apr 20 '15 at 16:08
Good catch. That's possibly an error in the dataset. Fixed it. — S Das, Apr 20 '15 at 16:10
It would be more typical to have 0-23 be the range, not 1-24. (Would hour 24 mean after midnight on the following day? If not, then would 1 mean 12:00 AM, and 2 mean 1:00 AM, and so on?) — David Robinson, Apr 20 '15 at 16:13
I agree. I have changed the reproducible example accordingly. — S Das, Apr 20 '15 at 16:17

score 1 · Accepted Answer · answered Apr 20 '15 at 16:16

In order to parse a date/time string of the "-"-separated format you're creating, you'll need to give a custom format, and pass it to parse_date_time. For example:

parse_date_time(paste(dat$D.YEAR, dat$D.MONTH, dat$D.DAY, dat$D.HOUR, dat$D.MINUTE, sep = "-"),
                "%Y-%m-%d-%H-%M")

Your new code would therefore look like:

library(lubridate)
dat$tstamp1 <- parse_date_time(paste(dat$YEAR, dat$MONTH, dat$DAY, dat$HOUR, dat$MINUTE, sep = "-"),
                "%Y-%m-%d-%H-%M")
dat$tstamp2 <- parse_date_time(paste(dat$D.YEAR, dat$D.MONTH, dat$D.DAY, dat$D.HOUR, dat$D.MINUTE, sep = "-"),
                                "%Y-%m-%d-%H-%M")

Then the following will get you the time difference in minutes:

dat$diff <- as.numeric(dat$tstamp2 - dat$tstamp1)

score 0 · Answer 2 · edited May 23 '17 at 12:02

You can try this:

library(lubridate)

dat$tstamp1 <- strptime(paste(dat$YEAR, dat$MONTH, dat$DAY, dat$HOUR, dat$MINUTE,sep = "-"),"%Y-%m-%d-%H-%M")

dat$tstamp2 <- strptime(paste(dat$D.YEAR, dat$D.MONTH, dat$D.DAY, dat$D.HOUR,  dat$D.MINUTE, sep = "-"),"%Y-%m-%d-%H-%M")

dat$diff <- as.POSIXct(dat$tstamp2) - as.POSIXct(dat$tstamp1)

Using strptime is faster and bit safer against unexpected data. You can read more about it here.

Difference in Timestamp

2 Answers2