3

This is my query in mongodb

db.order.find ({"ublExtensions.ublExtensions.extensionContent.Metadata
                .ModificationTime": "2012-02-04T01:58:27.861Z"});

It is fetching the result only for the particular time zone. The modificationTime is changing on a daily basis. I have a requirement where I need to pick the data which are updated y'day...means (sysdate -1 in sql) one day data.

How can I achieve this in mongodb? Please note the my Date is a String Type and I have a constraint where I can't change that. Someone suggested to use the ISODate.

Please help in resolving this issue.

James
  • 1,873
  • 3
  • 22
  • 28
user1214124
  • 41
  • 1
  • 1
  • 3
  • Im pretty sure your only solution will be to loop over every document and convert the date string to a Date so that you can test it properly. Mongo only sees it as a string. Is there any way you could store another proper date field at the time the doc is updated? Then you can simply query everything in UTC – jdi Feb 16 '12 at 16:11
  • Is there any date conversion functions in mongo like we have to_char, to_date in sql? – user1214124 Feb 16 '12 at 16:56
  • How there anyway we can use ISODate, Date() or new Date() to use it as sysdate-1 – user1214124 Feb 16 '12 at 16:57

3 Answers3

8

Mongodb stores its date objects in a bson format like: {$date: 1329415205151}

If you decide to store it in a string format, then it is the client-side responsibility to filter and process this value as mongo treats it like a string. You can convert your strings to date objects by referring to this other SO question: How do I convert a property in MongoDB from text to date type?

Its been widely recommended to either store all your dates in UTC, or, a consistent timezone possibly related to the local datacenter, and then convert your date values to the proper local timezone on the client.

You can store whatever Date value you want. The value of the date and the format of the date are two separate issues. If your constraints require you to store that string-based date format in the document, it would be recommended to also store a $date object as well at the time of update.

Community
  • 1
  • 1
jdi
  • 90,542
  • 19
  • 167
  • 203
5

I would recommend storing your dates as Date or ISODate on MongoDB since it would ease your life :)

If not possible, then on the client side (programming language specific driver) you should be able to parse the string to the appropiate Date type.

However, if you need your query to run on mongo shell, you could follow an strategy similar as described at Mongo Cookbook

1) Creating a var with today's date and yesterday's date: 

>var today = new Date();
>today
ISODate("2012-11-26T22:12:03.870Z")
>var yesterday = new Date()
>yesterday.setDate(today.getDate() - 1)
>yesterday
ISODate("2012-11-25T22:12:03.870Z")

2) Form a query for finding documents within a 24 hours period

>db.order.find( { "ublExtensions.ublExtensions.extensionContent.Metadata.ModificationTime" : { $gte : yesterday, $lt : today } } )
Ghasfarost
  • 742
  • 2
  • 8
  • 17
0

Why can't you just use $gt query and for comparing time you can use Date.now() and from that Date.now() you can use previous date, like if current date is "2017-05-16T02:08:48.419+05:30" then you will write the query 

db.collection.find({"createdAt": { $gte : new ISODate("2017-05-15T02:08:48.419+05:30") }})
Sudhanshu Gaur
  • 7,486
  • 9
  • 47
  • 94
  • 1
    This is basically exactly what @Ghasfarost said in his answer, except his answer started by recommending that the dates be stored as date objects instead of strings. This answer ignored the fact that they would first have to be stored as dates for a date query to work. The op said storing them as strings was a constraint they could not drop. – jdi Nov 11 '17 at 20:33
  • @jdi thanks, my bad I just didn't read whole answer by@Ghasfarost. – Sudhanshu Gaur Nov 12 '17 at 12:33