0

I want to calculate the duration between 2 date of a data frame which formatted as per below:

Start_date_time
22-JAN-16 04.56.14.325000000 PM

End_date_time
23-JAN-16 05.56.14.325000000 AM

I run the below command:

jm16$diff <- difftime(strptime(jm16$Start_date_time, format = "%d-%b-%Y %H.%M.%S"), 
                      strptime(jm16$End_date_time, format = "%d-%b-%Y %H.%M%S"),
                      units="mins")

however, it ends up the entire field of jm16$diff is "NA". Anybody please help to advise where I make mistake?

Thanks

Han

plannapus
  • 18,529
  • 4
  • 72
  • 94
user2778168
  • 193
  • 9
  • %Y is for four digit years - you need %y. You will also need to specify the AM/PM with %p – Richard Telford Apr 19 '16 at 09:30
  • Hi Thanks for reply. I changed the command to jm16$diff <- difftime(strptime(jm16$Start_date_time, format = "%d-%b-%y %i.%m.%s %p"), strptime(jm16$End_date_time, format = "%d-%b-%y %i.%m.%s %p"),units="mins"), but the result is still the same > summary(as.numeric(jm16$diff)) Min. 1st Qu. Median Mean 3rd Qu. Max. NA's NA NA NA NaN NA NA 1540335 – user2778168 Apr 19 '16 at 09:54

1 Answers1

0

The formula you need to use is:

jm16$diff <- difftime(strptime(jm16$End_date_time, format="%d-%b-%Y %I:%M.%OS %p"), 
                      strptime(jm16$Start_date_time, format="%d-%b-%Y %I:%M.%OS %p"), 
                      units="mins")

This corrects the following issues:

  1. difftime() takes difftime(end_time, start_time), not the other way around
  2. %I deals with hours in 1-12 as opposed to %H which deals with 0-23
  3. The hour is followed by a colon in your string, not a dot
  4. There is a fractional part to the seconds so %OS, instead of %S
  5. A " %p" at the end to take in the am/pm
plannapus
  • 18,529
  • 4
  • 72
  • 94
lampishthing
  • 74
  • 8
  • Hi Thanks for reply. I tried the formula you gave, but the following error appear: Error in as.POSIXlt.character(as.character(x), ...) : character string is not in a standard unambiguous format – user2778168 Apr 19 '16 at 11:07
  • Try it for an individual row? – lampishthing Apr 19 '16 at 11:08
  • Ah, and maybe lowercase y's – lampishthing Apr 19 '16 at 11:09
  • I tried the entire field with strptime(jm16$Start_date_time, format = "%d-%b-%Y %I.%M.%OS %p"), it return result as below: ..........[9913] NA NA NA NA [9917] "16-01-05 15:12:50 LMT" "16-01-05 15:12:50 LMT" "16-01-05 15:12:50 LMT" NA [9921] NA NA NA "16-01-05 22:10:20 LMT" [9925] "16-01-05 22:10:20 LMT" NA NA – user2778168 Apr 19 '16 at 11:15
  • tried again with lower "y" - strptime(jm16$Start_date_time, format = "%d-%b-%y %I.%M.%OS %p") - return result as below: ............[9917] "2016-01-05 15:12:50 SGT" "2016-01-05 15:12:50 SGT" "2016-01-05 15:12:50 SGT" NA [9921] NA NA NA "2016-01-05 22:10:20 SGT" [9925] "2016-01-05 22:10:20 SGT" NA NA NA – user2778168 Apr 19 '16 at 11:20
  • enter this: jm16$Start_date_time [9920] is the format correct? – lampishthing Apr 19 '16 at 11:25