Parse timestamp with a.m./p.m

Question

I have a file that formats time stamps like 25/03/2011 9:15:00 p.m.

How can I parse this text to a Date-Time class with either strptime or as.POSIXct?

Here is what almost works:

> as.POSIXct("25/03/2011 9:15:00", format="%d/%m/%Y %I:%M:%S", tz="UTC")
[1] "2011-03-25 09:15:00 UTC"

Here is what is not working, but I'd like to have working:

> as.POSIXct("25/03/2011 9:15:00 p.m.", format="%d/%m/%Y %I:%M:%S %p", tz="UTC")
[1] NA

I'm using R version 2.13.2 (2011-09-30) on MS Windows. My working locale is "C":

Sys.setlocale("LC_TIME", "C")

`?strptime` says that `%p` is "AM/PM indicator in the locale". http://stdcxx.apache.org/doc/stdlibref/time-put.html says in the C locale it is AM/PM, so I think you'll have to `sub("a.m","AM",...)` and `sub("p.m.","PM",...)` first. (There's probably a slick regular expression that would do it all in one go.) — Ben Bolker, Oct 25 '11 at 01:51
@BenBolker: I got your slick regular expression right here... :) — Joshua Ulrich, Oct 25 '11 at 01:54

score 21 · Accepted Answer · answered Oct 25 '11 at 01:50

It appears the AM/PM indicator can't include punctuation. Try it after removing the punctuation:

td <- "25/03/2011 9:15:00 p.m."
tdClean <- gsub("(.)\\.?[Mm]\\.?","\\1m",td)
as.POSIXct(tdClean, format="%d/%m/%Y %I:%M:%S %p", tz="UTC")
# [1] "2011-03-25 21:15:00 UTC"

score 1 · Answer 2 · answered Jan 09 '17 at 22:44

Just came across this, as another option you can use stringr package.

library(stringr)
data$date2 <- str_sub(data$date, end = -4) 
# this removes the punctuation but holds onto the A/P values
data$date2 <- str_c(data$date2, 'm') 
# adds the required m

Parse timestamp with a.m./p.m

2 Answers2

Linked