Convert MongoDB character as R Date

Question

I tried to convert MongoDB date to R date Object. I use

library(RMongo)
library(data.table)

mongo<-mongoDbConnect("test", host = "127.0.0.1", port = "27017")
event<-dbGetQuery(mongo, "event", "", 0, 1000)
data<-as.data.table(event)

date<-data$date return the vector:

[1] "Fri Oct 28 13:15:00 CEST 2016" "Fri Oct 28 16:00:00 CEST 2016" "Fri Nov 04 18:30:00 CET 2016"  "Fri Nov 04 18:45:00 CET 2016" 

I am not able to convert this vector. I tried the following code:

as.Date(date, format = "%a %b %d %H:%M:%S %Y")
[1] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA

strptime(date, format = "%a %b %d %H:%M:%S %Y")
[1] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA

I also tried to modify the format using

s.POSIXct(date, "%a %b %d %H:%M:%S %Z %Y")
Error in as.POSIXct.default(date, "%a %b %d %H:%M:%S %Z %Y")

Can someone kindly explain how I can achieve this, and explain what I am doing wrong ..

Many thanks in advance

Answer 1

I had the same issue, and I found 2 possible solutions:

1. You con use the library mongolite instead of RMongo, which gives you the dates in the format "2016-01-01 01:00:00". The you just format them, for example calling format(yourTime, "%Y-%m-%d %H:%M:%OS3") to recover the millis.
2. Using both libraries, you can use the aggregation framework and in the project stage convert your date to string, for example like this:"$dateToString": { "format": "%Y-%m-%d %H:%M:%S.%L", "date": "$yourTime" }, then you format them again like in the previous case.

I hope it helps.

Farewell