0

I am trying to load a dataset into r and change the type of a column into datetime.

strptime, as.POSIXct or as.Date for different cases should work;

This is my code:

a <- structure(list(DATE = c("01/01/2011 12:00:00", "01/02/2011 12:00:00", 
   "01/03/2011 12:00:00", "01/04/2011 12:00:00", "01/05/2011 12:00:00", 
   "01/06/2011 12:00:00"), VAL = c(65.34447917, 65.23983333, 65.03183333, 
   64.89107292, 64.83333333, 64.848625), id = c("VT1-1", "VT1-1", "VT1-1", 
   "VT1-1", "VT1-1", "VT1-1")), .Names = c("DATE", "VAL", "id"), row.names = c(NA, -6L), 
   class = c("tbl_df", "tbl", "data.frame"))

b1 <- as.POSIXct(a$DATE, format = "%m/%d/%y %H:%M:%S")
b2 <- strptime(a$DATE,"%m/%d/%Y %H:%M/%S")

But they just return NA. It is most probably a typo; but how this can be avoided while dealing with different date-time formats?

M--
  • 25,431
  • 8
  • 61
  • 93
  • 2
    You had a typo -- you used `"%m/%d/%y %H:%M/%S"` with a slash at the end. You need `"%m/%d/%y %H:%M:%S"` with a colon. But see my answer. – Dirk Eddelbuettel Mar 24 '17 at 20:01

1 Answers1

1
R> library(anytime)
R> anytime(a$DATE)
[1] "2011-01-01 12:00:00 CST" "2011-01-02 12:00:00 CST" "2011-01-03 12:00:00 CST"
[4] "2011-01-04 12:00:00 CST" "2011-01-05 12:00:00 CST" "2011-01-06 12:00:00 CST"
R>

The anytime package was built to make this easy -- you don't need to supply a format you may get wrong, it simply tries a number of plausible ones. For "reasonable" input, it just works.

Dirk Eddelbuettel
  • 360,940
  • 56
  • 644
  • 725
  • I am afraid it does not work unless you consider the dmy standard as unreasonable. It works ok for the American mdy standard, but I am afraid dmy is the second most often used (after ymd). It does not have a problem with ymd. It fails as well on single digits like 6/9/2018, it requires trailing zeros. I think it is far safer to provide format rather than relying on heuristics – Slav Feb 13 '18 at 13:30
  • 1
    The problem is that you cannot distinguish mm-dd-yyyy and dd-mm-yyyy unless the author classified it, and that does make it unreasonable because it is error-prone. Hence the preference for ISO dates: yyyy-mm-dd. And the documentation is clear about it. – Dirk Eddelbuettel Feb 13 '18 at 13:33
  • 1
    so in other words - you need to supply a format, even if you might get it wrong as opposed to what you said. Otherwise it is error-prone. Not much different from the base solution and one package less – Slav Feb 21 '18 at 13:22
  • 2
    @Slav Nonsense. You are attempting to twist my words. Did you bother reading the [anytime help page (eg here at RDocumentation)](https://www.rdocumentation.org/packages/anytime/versions/0.3.0/topics/anytime) ? I can't make the issue of m/d/y vs d/m/y go away but I *do* a) document a strong preference and recommendation for ISO8601, and b) state that in the case of conflict m/d/y is used. End of story. – Dirk Eddelbuettel Feb 21 '18 at 13:24