working with Date type in mongoDB

[Nitesh Kumawat]

I have imported a excel data to mongoDB. There the date got imported as string. I want date string field to be converted or want to add new field which would be of type date. I am not getting how to do it. I look for some SO posts but I am getting below issue:

I tried to do 
var x = new ISODate("1/2/2014");
it is giving shell error invalid iso date at src/mongo/shell/types.js

Can someone help please?

another thing is if I do 

var x = new Date( )

it gives ISO date with timezone.. 

how to work if I only want Date, I am not interested in TimeZone?

> var x = new Date(2010, 1, 1)

> x

ISODate("2010-01-31T18:30:00Z")

I am getting 31st Jan instead of 1st jan... 

really struggeling to solve this.

-Nitesh

[Asya Kamsky]

The date type supported in MongoDB will be to the second granularity so you will get ISODate() with 0's filled in past the day as you saw (the reason it's off by 5.5 hours is because it's showing you UTC or Zulu time - I'm guess you're in India time zone?)

If you want it as date your only option is ISODate() - otherwise you can store it as a string that's well ordered and can be used for comparisons (for example "2014/01/14" is one of those formats, "1/2/2014" is *not* one).

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/bd1ee19d-6193-486b-82ce-791d25f10943%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Victor Hooi]

Hi Nitesh,

Everything Asya said is correct.

Regarding your error message, the constructor for ISODate expects a valid ISO8601-format date. For example, the below will work:

> var x = new ISODate("2014-05-05")

> x
ISODate("2014-05-05T00:00:00Z")

Essentially, you have two approaches to storing date-only fields:

1. Use MongoDB ISODate, and have your application code handle ignoring anything past the day field. You will also need to ensure all of your dates are converted to a uniform timezone (e.g. UTC) prior to importing.
   
2. Use a string field and store a well-ordered date - e.g. YYYYMMDD.
   

Further, in Excel, you can control how dates are displayed. Select your cells, then hit Ctrl + 1 (Windows) or Command + 1 (OSX). Then in the Format Cells dialog, select the appropriate date format.

If you can provide more details on the exact steps you are taking to import this data from Excel to MongoDB, we can help you better.

Please let me know if you have any queries about any of the above.

Regards,

Victor

[Nitesh Kumawat]

Thanks Victor and Asya.

So what I understood is I need to format the date in excel before importing. I tried a sample in mongo shell as below

> var x = new ISODate("20140202")

> x

ISODate("2014-02-02T00:00:00Z")

means, If I want to work only with dates then I could use proper format date and use ISODate field. because now it does not have time component in this.

can you let me know what is the difference between date and ISODate data type in mongoDb?

Thanks

Nitesh

You received this message because you are subscribed to a topic in the Google Groups "mongodb-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mongodb-user/CV91VFPuM3E/unsubscribe.
To unsubscribe from this group and all its topics, 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/b00c5666-75c0-41a0-a7a8-34103bff5bd1%40googlegroups.com.

[Asya Kamsky]

Not sure what you mean - there is only one date type (it's called ISODate()).

There is also a type called Timestamp but we haven't been discussing it in this thread...

If you are referring to JS Date() constructor, that will result in the same ISODate() in the database as you saw.

Asya

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CAFUPUWyH2NGbrEHZ1TuVQbehqXcYBi_7kGYy846%2Bxk3Nwygomw%40mail.gmail.com.