0

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

FRieux
  • 1
  • 1
  • you have a field in mongodb that stores date and now you want to read and convert it to R date. Am i Correct ? – satish chennupati Nov 24 '16 at 14:34
  • Exactly, this field is a iso date in mongodb. – FRieux Nov 25 '16 at 08:59
  • ok few examples for your reference 1. # mongo.bson.from.list automatically converts R primitive data types(integer, numeric, logical, character) into MongoDB data types. # You have to make some extra job for Date types. # To build bson with ISODate data you shoudl pass it as POSIXct object: date_string<- "2014-10-11 12:01:06" query<-mongo.bson.from.list(list(date=as.POSIXct(date_string,tz='GMT'))) # pay attention to timezone argument # note, that internall MongoDB strores dates in unixtime format: query – satish chennupati Nov 25 '16 at 12:04
  • 2. d <- as.integer(as.POSIXct("2016-11-11")) * 1000 data <- mong$find(paste0('{"dte":{"$gt": { "$date" : { "$numberLong" : "', d, '" } } } }')) – satish chennupati Nov 25 '16 at 12:06
  • Thanks for your help. I am trying to understand yours examples to apply them on my context. To be sure, i am read-only on the mongo data base and i query the database to extract data.table event with a variable containing some date in the format "Fri Oct 28 13:15:00 CEST 2016". I try to convert the date in POSIxct in R, but i have an error (return NA). Then if i understand your example i need to modify the query to mongoDB to convert Date ? – FRieux Nov 25 '16 at 14:38
  • sounds like some idea. good luck. if my examples help you dont forget to give an upvote. – satish chennupati Nov 25 '16 at 15:23

1 Answers1

0

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

vantesllar
  • 469
  • 4
  • 13