-1

I have several variables that exist in the following format:

/Date(1353020400000+0100)/

I want to convert this format to ddmmyyyy. I found this solution for the same problem using php, but I don't know anything about php, so I'm unable to convert that solution to what I need, which is a solution that I can use in R.

Any suggestions?

Thanks.

Community
  • 1
  • 1
Tkelly
  • 187
  • 1
  • 2
  • 11
  • 1
    Do you care to share what date format that is? `+0100` seems to hint at a UTC offset while the long number looks like milliseconds since epoch or something. – AlexR Dec 01 '16 at 22:09
  • 2
    This is my vote for "Worst Title Ever" – WillardSolutions Dec 01 '16 at 22:14
  • You have requested two different output formats. Why not instead amend the question so that you can accept one of the answers below and do your own reading on `?format.POSIXct` – IRTFM Dec 01 '16 at 22:25
  • 1
    That looks like a json formatted date. I've used package RJSONIO to read those. You could take a look [here](http://stackoverflow.com/questions/27307288/simplifying-a-posix-node-with-rjsoniofromjson) and try to alter the code to fit your scenario. – Rich Scriven Dec 01 '16 at 22:25

3 Answers3

3

If the format is milliseconds since the epoch then anytime() or as.POSIXct() can help you:

R> anytime(1353020400000/1000)
[1] "2012-11-15 17:00:00 CST"
R> anytime(1353020400.000)
[1] "2012-11-15 17:00:00 CST"
R>

anytime() converts to local time, which is Chicago for me. You would have to deal with the UTC offset separately.

Base R can do it too, but you need the dreaded origin:

R> as.POSIXct(1353020400.000, origin="1970-01-01")
[1] "2012-11-15 17:00:00 CST"
R>
Dirk Eddelbuettel
  • 360,940
  • 56
  • 644
  • 725
  • What package is `anytime` in? BTW: I didn't know you could pass a character directly to `origin`. Always used `as.Date("1970-01-01")`... – AlexR Dec 01 '16 at 22:31
  • @AlexR - it's tricky like that - from the help files `origin - a Date object, or something which can be coerced by as.Date(origin, ...) to such an object.` – thelatemail Dec 01 '16 at 22:33
  • @thelatemail Luckily we usually have stuff that can be parsed by the nicely compact `lubridate::ymd()` and friends. But it's nice to know because other code quickly gets ugly. – AlexR Dec 01 '16 at 22:34
3

As far as I can tell from the linked question, this is milliseconds since the epoch:

x <- "/Date(1353020400000+0100)/"
spl <- strsplit(x, "[()+]")
as.POSIXct(as.numeric(sapply(spl,`[[`,2)) / 1000, origin="1970-01-01", tz="UTC")
#[1] "2012-11-15 23:00:00 UTC"

If you want to pick up the timezone difference as well, here's an attempt:

x <- "/Date(1353020400000+0100)/"
spl <- strsplit(x, "(?=[+-])|[()]", perl=TRUE)

tzo <- sapply(spl, function(x) paste(x[3:4],collapse="") )
dt  <- as.POSIXct(as.numeric(sapply(spl,`[[`,2)) / 1000, origin="1970-01-01", tz="UTC")

as.POSIXct(paste(format(dt), tzo), tz="UTC", format = '%F %T %z')
#[1] "2012-11-15 22:00:00 UTC"
thelatemail
  • 91,185
  • 12
  • 128
  • 188
  • Anyone care to explain the downvote? Answer seems to work for me. – thelatemail Dec 01 '16 at 22:31
  • Most interesting... (-1) is gone on my answer now. For me, all answers have their benefits. Yours appears to be the only vectorized one as per now (+1)... – AlexR Dec 01 '16 at 22:33
  • @AlexR - they should all be vectorized I believe, including yours. – thelatemail Dec 01 '16 at 22:34
  • Wow, I didn't even try and usually something goes wrong with the code written for scalar input. – AlexR Dec 01 '16 at 22:36
  • 1
    You can capture the timezone offset too, with a little work: `tz = paste0('Etc/GMT', gsub('.*([+-]).*', '\\1', x), as.numeric(sapply(spl, \`[[\`, 3)) / 100)` – alistaire Dec 01 '16 at 22:39
  • @alistaire - yeah, that's why I went with the `strsplit` originally, so I could get the extra part - might have to adjust my split to take into account - or + though. – thelatemail Dec 01 '16 at 22:40
  • 1
    Yeah, I don't think there's a good way to split it short of lookarounds, which seems gratuitous. I worked up the whole thing with regex, but it's a bit long: `as.POSIXct(as.numeric(gsub('\\D*(\\d+)[+-].+', '\\1', x)) / 1000L, origin = as.POSIXct('1970-01-01 00:00:00'), tz = paste0('Etc/GMT', gsub('.*([+-]).*', '\\1', x), as.integer(gsub('.*[+-](\\d{2}).+', '\\1', x))))` – alistaire Dec 01 '16 at 22:42
  • @alistaire `stringr::str_match(text, "^/Date\\((\\d+)([+-])(\\d{4})\\)/$")` will capture the data in groups 2, 3 and 4. – AlexR Dec 01 '16 at 22:53
  • @thelatemail For the timezone one to work accurately, you need to specify `tz = 'UTC'` for `dt` and `format = '%F %T %z'` for the recalculation. Looking good, though. – alistaire Dec 01 '16 at 23:02
2

The package lubridate can come to the rescue as follows:

as.Date("1970-01-01") + lubridate::milliseconds(1353020400000)

Read: Number of milliseconds since epoch (= 1. January 1970, UTC + 0)

A parsing function can now be made using regular expressions:

parse.myDate <- function(text) {
    num <- as.numeric(stringr::str_extract(text, "(?<=/Date\\()\\d+"))
    as.Date("1970-01-01") + lubridate::milliseconds(num)
}

finally, format the Date with format(theDate, "%d/%m/%Y %H:%M")

If you also need the time zone information, you can use this instead:

parse.myDate <- function(text) {
    parts <- stringr::str_match(text, "^/Date\\((\\d+)([+-])(\\d{4})\\)/$")
    as.POSIXct(as.numeric(parts[,2])/1000, origin = "1970-01-01", tz = paste0("Etc/GMT", parts[,3], as.integer(parts[,4])/100))
}
AlexR
  • 2,412
  • 16
  • 26