0

I have some dates and times that for some reason switch between formats at the turn of a new month. I would like to convert them to be consistently Day, Month, Year (19 rather than 2019) and 24 hr clock with seconds. I have managed to switch half using strptime but does not work for entire dataset.

Here is an example:

datetime <- c("02/28/19 11:39:00 PM", "02/28/19 11:40:00 PM", "02/28/19 11:41:00 PM", 
"02/28/19 11:42:00 PM", "02/28/19 11:43:00 PM", "02/28/19 11:44:00 PM", 
"02/28/19 11:45:00 PM", "02/28/19 11:46:00 PM", "02/28/19 11:47:00 PM", 
"02/28/19 11:48:00 PM", "02/28/19 11:49:00 PM", "02/28/19 11:50:00 PM", 
"02/28/19 11:51:00 PM", "02/28/19 11:52:00 PM", "02/28/19 11:53:00 PM", 
"02/28/19 11:54:00 PM", "02/28/19 11:55:00 PM", "02/28/19 11:56:00 PM", 
"02/28/19 11:57:00 PM", "02/28/19 11:58:00 PM", "02/28/19 11:59:00 PM", 
"03/01/2019 00:00", "03/01/2019 00:01", "03/01/2019 00:02", "03/01/2019 00:03", 
"03/01/2019 00:04", "03/01/2019 00:05", "03/01/2019 00:06", "03/01/2019 00:07", 
"03/01/2019 00:08", "03/01/2019 00:09", "03/01/2019 00:10", "03/01/2019 00:11", 
"03/01/2019 00:12", "03/01/2019 00:13", "03/01/2019 00:14", "03/01/2019 00:15", 
"03/01/2019 00:16", "03/01/2019 00:17", "03/01/2019 00:18", "03/01/2019 00:19", 
"03/01/2019 00:20", "03/01/2019 00:21", "03/01/2019 00:22", "03/01/2019 00:23", 
"03/01/2019 00:24", "03/01/2019 00:25", "03/01/2019 00:26", "03/01/2019 00:27", 
"03/01/2019 00:28", "03/01/2019 00:29")
PharmR
  • 260
  • 1
  • 3
  • 12
  • Some relevant discussion here on dealing with multiple formats - https://stackoverflow.com/questions/42102462/r-changing-date-format-in-one-column-that-contains-two-date-formats/42102572 – thelatemail May 29 '19 at 22:15
  • thanks @thelatemail. Your suggestion in the post worked other than that the year up until the change of the month became 0019. – PharmR May 29 '19 at 22:24
  • 1
    `pmax( as.POSIXct(datetime, format="%m/%d/%y %I:%M:%S %p", tz="UTC"), as.POSIXct(datetime, format="%m/%d/%Y %H:%M", tz="UTC"), na.rm=TRUE )` will take care of both correctly. – thelatemail May 29 '19 at 22:25

1 Answers1

1

One possible approach is

lubridate::mdy_hms(datetime, truncated = 1L)

which returns 

 [1] "2019-02-28 23:39:00 UTC" "2019-02-28 23:40:00 UTC" "2019-02-28 23:41:00 UTC" "2019-02-28 23:42:00 UTC"
 [5] "2019-02-28 23:43:00 UTC" "2019-02-28 23:44:00 UTC" "2019-02-28 23:45:00 UTC" "2019-02-28 23:46:00 UTC"
 [9] "2019-02-28 23:47:00 UTC" "2019-02-28 23:48:00 UTC" "2019-02-28 23:49:00 UTC" "2019-02-28 23:50:00 UTC"
[13] "2019-02-28 23:51:00 UTC" "2019-02-28 23:52:00 UTC" "2019-02-28 23:53:00 UTC" "2019-02-28 23:54:00 UTC"
[17] "2019-02-28 23:55:00 UTC" "2019-02-28 23:56:00 UTC" "2019-02-28 23:57:00 UTC" "2019-02-28 23:58:00 UTC"
[21] "2019-02-28 23:59:00 UTC" "2019-03-01 00:00:00 UTC" "2019-03-01 00:01:00 UTC" "2019-03-01 00:02:00 UTC"
[25] "2019-03-01 00:03:00 UTC" "2019-03-01 00:04:00 UTC" "2019-03-01 00:05:00 UTC" "2019-03-01 00:06:00 UTC"
[29] "2019-03-01 00:07:00 UTC" "2019-03-01 00:08:00 UTC" "2019-03-01 00:09:00 UTC" "2019-03-01 00:10:00 UTC"
[33] "2019-03-01 00:11:00 UTC" "2019-03-01 00:12:00 UTC" "2019-03-01 00:13:00 UTC" "2019-03-01 00:14:00 UTC"
[37] "2019-03-01 00:15:00 UTC" "2019-03-01 00:16:00 UTC" "2019-03-01 00:17:00 UTC" "2019-03-01 00:18:00 UTC"
[41] "2019-03-01 00:19:00 UTC" "2019-03-01 00:20:00 UTC" "2019-03-01 00:21:00 UTC" "2019-03-01 00:22:00 UTC"
[45] "2019-03-01 00:23:00 UTC" "2019-03-01 00:24:00 UTC" "2019-03-01 00:25:00 UTC" "2019-03-01 00:26:00 UTC"
[49] "2019-03-01 00:27:00 UTC" "2019-03-01 00:28:00 UTC" "2019-03-01 00:29:00 UTC"

for the given sample dataset.

Uwe
  • 41,420
  • 11
  • 90
  • 134
  • This works very well, thank you! How can I remove the timezone element? – PharmR May 29 '19 at 22:24
  • 1
    @PharmR - you can't really I don't think. Either the timezone is specified explicitly or it is the default of your system timezone.You could `format()` the output back to text, but then you lose all the date comparison possibilities. – thelatemail May 29 '19 at 22:30
  • OK no problem. Thank you very much. Apologies also, I missed the post you pointed me towards in searches. – PharmR May 29 '19 at 22:33