How to calculate monthly time differences?

Question

A simple question:

# Given the following initial time
t0 <- strptime('17/Nov/2010:08:04:21',format='%d/%b/%Y:%H:%M:%S')
# ...and final time
t1 <- strptime('21/Jan/2011:13:11:04',format='%d/%b/%Y:%H:%M:%S')
# I can calculate the difference of time
difftime(t1,t0,units='hours')
# Time difference of 1565.112 hours

BUT, I also wish to know how many hours correspond to November, to December, and to January separately. Of course, I have to do that for thousands of data, from different years, but looping is not my problem. Any idea? Thanks!

can u please little bit clear on what output u needed exactly? — sai saran, Nov 09 '18 at 13:32

mysteRious · Answer 1 · 2018-11-09T15:35:00.317

Is this what you mean? Here is an example taking differences per month:

t0 <- strptime('17/Nov/2010:08:04:21',format='%d/%b/%Y:%H:%M:%S')
t1 <- strptime('21/Jan/2011:13:11:04',format='%d/%b/%Y:%H:%M:%S')

seq.firsts <- seq.Date(as.Date(t0),as.Date(t1),by='month')
boundaries <- paste0(format(seq.firsts, "%Y-%m"),"-01")

This gives you the month boundaries:

> boundaries
[1] "2010-11-01" "2010-12-01" "2011-01-01"

But now you have to put them in order and throw out any ones prior to t0 if they got in there:

timelist <- c(t0,t1,boundaries)
ab <- sort( timelist[timelist >= t0] ) # adjust boundaries; throw out beyond t0 and t1

And now you can difftime between each pair in that list:

> for (i in 1:(length(ab)-1)) { print(abs(  difftime(ab[i],ab[i+1])   ))  }
Time difference of 81.59102 days
Time difference of 30.04167 days
Time difference of 31 days

Or add units:

> for (i in 1:(length(ab)-1)) { print(abs(  difftime(ab[i],ab[i+1],units='hours')   ))  }
Time difference of 1958.184 hours
Time difference of 721 hours
Time difference of 744 hours

@mysteRious, i *think* there is probably a bug in your code. `Time difference of 31 days` cannot be right since we are looking at the number of days from the beginning of the month till the 21st of January. — Jrakru56, Nov 09 '18 at 14:39

jay.sf · Answer 2 · 2018-11-09T16:11:08.427

You could take advantage of as.POSIXct() calculating with seconds.

# create a time vector 
sec <- seq(1, as.numeric(dif)*3.6e3, length.out=dif)

# create time table
t.dif.1 <- table(strftime(as.POSIXct(sec, origin=t0), format="%Y-%m"))
t.dif.1 <- rbind(hours=t.dif.1, "cumulated hours"=cumsum(t.dif.1)) # add cumulated hours

Yields

> t.dif.1
                2010-11 2010-12 2011-01
hours               327     744     495
cumulated hours     327    1071    1566

score 1 · Accepted Answer · answered Nov 09 '18 at 14:37

Something like this would work:

   d <- force_tz(seq(t0, t1, by="month"), tzone ="EST")

  Start<- list.append(d[[1]], lapply(d[2:length(d)], function(e) floor_date(e,"month")))

  #there is probably a cleaner way to do the next step than using  double 
  #rev() but it is get around some issues with unlist and lossing my datetime format

      End<- rev(list.append(d[[length(d)]], lapply(rev(d)[2:length(d)], function(e) ceiling_date(e,"month"))))
      df<-data.frame(Start ,End )
      df$diff<- difftime(df$End, df$Start, units ="days")
      df$diff_hours <- difftime(df$End, df$Start, units ="hours")
  df

                Start                 End          diff     diff_hours
1 2010-11-17 08:04:21 2010-12-01 00:00:00 13.66365 days 327.9275 hours
2 2010-12-01 00:00:00 2011-01-01 00:00:00 31.00000 days 744.0000 hours
3 2011-01-01 00:00:00 2011-01-17 08:04:21 16.33635 days 392.0725 hours

For those interested, the `unlist` *issue* is discussed here: https://stackoverflow.com/questions/15659783/why-does-unlist-kill-dates-in-r — Jrakru56, Nov 09 '18 at 14:45

How to calculate monthly time differences?

3 Answers3