converting Oracle timestamps to ISODate

[32Quick]

Hi,

I exported Oracle data to a CSV file and subsequently imported it into MongoDB.  The documents contain fields like  "SUBMIT_TIME" : "1/5/2011 5:08:20.000000 PM -08:00".  I need to convert the date to ISODate format in order update the documents with the results of some date arithmetic.   How can I do this?  I have considered using an imtermediate Perl script after exporting and before importing and converting the date to ISODate in Oracle before exporting, but I am hoping that there is an elegant way to do this with the 2.8M documents already in MongodB.

thanks in advance, 32Quick

[Asya Kamsky]

Probably the easiest way to do this, assuming you only need to do it once is via aggregation framework.

Basically you can run db.collection.aggregate({$project:{field1:1,field2:1,field3:1, [...], submitTime:{$<do-some-conversion-here}, etc}}, {$out: "newCollectionName"});

The question is what is the conversion that needs to be done to turn "$SUBMIT_TIME" into new field which is its equivalent ISODate().

I've got a number of write-ups on manipulating various formats of time here, but this is easiest if you know the exact format and the date range represented by these strings.

Basically you need to "normalize" the string format and then select appropriate numbers for year/month/date and construct a new ISODate via date arithmetic.   It's a bit of a pain, but it is doable.

To be honest, it *might* be simpler to just do it in JavaScript vis a script that you would run in the shell but on the server machine (I assume this is a single mongod and not a sharded cluster?)

Asya

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/bd1efd2c-1705-4edf-b8a1-68bd22185fd9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

{ "name" : "Asya Kamsky",
  "place" : [ "New York", "Palo Alto", "Everywhere else" ],
  "email" : "as...@mongodb.com",
  "blog" : "http://www.askasya.com/",
  "twitter": "@asya999" }