1

I'm fairly new to using R and I have a numeric date variable in my dataset that is quite difficult to convert to standard as.Date format in R.

Currently, the Date variable includes a numeric value with 11 numbers and I have tried using several examples of code on how to convert the date variable. However, for some reason no code is able to convert the year correctly, the months and day are correctly converted but not the actual year.

# 
df$Date <- data.frame(column = c(13643596800, 13671590400, 13674441600, 13655088000,
                            13776220800, 13700793600, 13684118400, 13662172800, 13691116800))


# I have tried several version of as.Date, as_date and as_datetime. 
df$Date <- as.Date(df$column, origin = "1970-01-01")

As evident from the code, the code is incorrectly converting the year variable. Would appreciate all the help I can get.

Heala45
  • 161
  • 11
  • 1
    What is this format ? Is this in milliseconds ? If yes, it seems that the question has already been asked : https://stackoverflow.com/questions/31050756/how-to-convert-unix-timestamp-milliseconds-and-timezone-in-r – Basti Jun 05 '23 at 13:19
  • as.Date(as.POSIXct(df$column/1000, origin="1970-01-01")) – user12256545 Jun 05 '23 at 15:16
  • Thank you for these comments. have tried similar code were the time has been converted into either days, seconds or milliseconds. None seems to work, when using milliseconds, the year variable is converted to the year 2400 something for all observations. So presume that milliseconds is also incorrect since year 2400 is clearly wrong. – Heala45 Jun 07 '23 at 09:36

1 Answers1

0

You need to figure out exactly what those numbers represent. as.Date(x, origin = "1970-01-01") will interpret x as "days since origin." Because your numbers are ~13643596800, that will put you in the year 37356869.

In other words, it's not a matter of hunting for the right function, but rather understanding the encoding of those numeric dates.

Arthur
  • 1,248
  • 8
  • 14
  • I agree with this, but I figured that it would be possible to figure this out from guessing and trying different date times from lubridate, since there are not infinite possibilities for what these numbers represent. – Heala45 Jun 07 '23 at 09:37
  • Another reason for posting the question, without knowing the representation of the time variable was because older R packages claimed that the conversion from numeric Date variable to standard format might be possible with perhaps other functions such as ymd() or anydate(). – Heala45 Jun 07 '23 at 12:09