0

I have a set of date times in my DB, generated by Javascript new Date(). in R, I read them into data frames as character, thereafter I need to convert them to POSIXct date times, but any combination of format that I try returns NA, any idea how to fix it? thanks

As an example, here is some of data I have:

> datetimes = c("Thu Dec 01 2016 14:53:38 GMT+0100 (CET)", "Thu Dec 01 2016 14:54:38 GMT+0100 (CET)", "Thu Dec 01 2016 14:55:38 GMT+0100 (CET)")
> class(datetimes)
[1] "character"
> c_datetimes = strptime(datetimes, format = '%a %b %d %Y %H:%M:%S')
> c_datetimes
[1] NA NA NA
ePezhman
  • 4,010
  • 7
  • 44
  • 80
  • *Don't* use such Javasript *strings*. Use Date's `toJSON()` to get a date in the unambiguous ISO8601 format. Anything else will faile one way or another. – Panagiotis Kanavos Feb 14 '17 at 12:33
  • How were these strings generated? What database are you using? It's actually an ugly bug to store dates as strings (for the obvious reason). Can you fix the code that generates them? If the database actually contains `datetimeoffset` or `datetime` objects, could it be your query that converts them to text? – Panagiotis Kanavos Feb 14 '17 at 12:36

2 Answers2

4

First, the database should store actual datetime values, not strings. If this can't be fixed, the code that generates the data should be modified to return ISO8601 strings. Just call Date.toJSON() or the identical toISOString() to get a string in the ISO8601 form: 2017-02-14T12:55:58.376Z.

As the name implies, Json dates are in this format. All REST APIs expect such a parameter too. Anything else simply covers up the problem.

The reason you can't parse the current text is that you are probably in a non-English locale. You can disable localized parsing by setting LC_TIME to C.

Once you do that, you can parse the text wtih the '%a %b %d %Y %H:%M:%S GMT%z' format string. Note GMT and %z. The GMT literal ensures that GMT is ignored in the string. %z will parse the offset.

The snippet:

datetimes = c("Thu Dec 01 2016 14:53:38 GMT+0100 (CET)", 
              "Thu Dec 01 2016 14:54:38 GMT+0100 (CET)", 
              "Thu Dec 01 2016 14:55:38 GMT+0100 (CET)")
Sys.setlocale("LC_TIME", "C")
strptime(datetimes, format = '%a %b %d %Y %H:%M:%S GMT%z')

Will return :

[1] "2016-12-01 15:53:38" "2016-12-01 15:54:38" "2016-12-01 15:55:38"

You'll note that the offset was taken into account to generate the correct local time for my machine, which is at +2:00 during winter.

UPDATE

Both toJSON() and toISOString() return UTC time. If you want to preserve the offset information and the data was generated using Javascript you may have to use moment.js to generate strings with the offset, as shown here :

var m = moment();    // get "now" as a moment
var s = m.format();  // the ISO format is the default so no parameters are needed

// sample output:   2013-07-01T17:55:13-07:00
Community
  • 1
  • 1
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
2

Probably your locale settings are not English:

datetimes = c("Thu Dec 01 2016 14:53:38 GMT+0100 (CET)", "Thu Dec 01 2016 14:54:38 GMT+0100 (CET)", "Thu Dec 01 2016 14:55:38 GMT+0100 (CET)")
Sys.setlocale("LC_TIME", "C")
strptime(datetimes, format = '%a %b %d %Y %H:%M:%S GMT%z', tz = "GMT") #choose wichever timezone you like here
[1] "2016-12-01 13:53:38 GMT" "2016-12-01 13:54:38 GMT" "2016-12-01 13:55:38 GMT"
Roland
  • 127,288
  • 10
  • 191
  • 288