CODEWITHSUNDEEP
mongodb
Crash Override
Crash Override

Reputation: 1427

MongoDB comparing dates only without times

How do I query for a date field in MongoDB with the date only, and not times? For example, if MongoDB stores July 7, 2015 with any time, I want to match that day only with today's date.

In MySQL, I would do SELECT col1, col2 FROM table WHERE DATE(date_field) = DATE(NOW());

Notice how MySQL has a function to change the date_field to the date only during matching. Can I do this in MongoDB with a similar function during matching? Like Collection.find({ dateonly(datetimefield): dateonly(new Date() }); or something of the like?

I understand Mongo stores date/times in GMT, so I would also have to convert the stored GMT date+time from GMT to Local before matching the date only, but to get me started, would like to know if I can strip the time off of a date match. For example, if GMT now is July 8 at 03:00 but Local Eastern Time US is July 7 at 23:00 then I would want to match July 7.

Upvotes: 42

Views: 95504

Answers (6)

Crash Override
Crash Override

Reputation: 1427

Updated 2018-06-26 fixed up the code to use moment() instead of new Date()

I solved this by use MomentJS Timezone (http://momentjs.com/timezone/) to convert a local date/time to a date-only numerical field, then I store the date as a number.

In my javascript code (outside of MongoDB):

var localDateOnly = function(timezone, d) {
  if (d == undefined) { d = new Date(); } // current date/time
  return Number( moment(d).tz(timezone).format("YYYYMMDD") );
}

Then I store a date-only field in the Mongo record.

var myDate = localDateOnly("America/New_York");  // today, in YYYYMMDD number
db.birthdays.insert(
  { dateonly: myDate, event: "This day is my birthday!" }
);

Then in my Javascript code, I can easily query today, tomorrow, specific days, etc.

// today
var myDate = localDateOnly("America/New_York");
db.birthdays.find( { dateonly: myDate } );

// tomorrow
var myDate = localDateOnly(
  "America/New_York",
  moment().add( 1, "days" )
);  // tomorrow
db.birthdays.find( { dateonly: myDate } );

// someone wants to know birthdays on the calendar on July 15, 2015,
// no timezone math needed, just type the date in YYYYMMDD format
db.birthdays.find( { dateonly: 20150715 } );

Hope this helps someone. Decided to store as a number for performance and validity checking. Before insert, I check for a valid date using the moment package as well:

moment( 20150715, "YYYYMMDD", true ).isValid()  // returns true & allowed to store in the database
moment( 150715, "YYYYMMDD", true ).isValid()  // returns false, don't insert into db

Code was cleaner when storing integers that represented dates, and made it easy for mongo to find ranges. Like, find birthdays in 2015 would be {"$gte": 20150101, "$lte": 20151231} or {"$gte": 20150000, "$lte": 20159999}.

Upvotes: 9

Liphtier
Liphtier

Reputation: 662

It's possible with $expr

{ $expr: {$eq: ["2021-03-29", { $dateToString: {date: "$dateField", format: "%Y-%m-%d"}}]}}

Upvotes: 17

Atikur Rahman Sabuj
Atikur Rahman Sabuj

Reputation: 436

You can extract the date as string at any given format and compare it with your date at that format using aggregation pipiline

$addFields: { "creationDate":  {$dateToString:{format: "%Y-%m-%d", date: "$createdAt"}}}},
{$match :  { creationDate:  {$eq: req.query.date}}

Upvotes: 18

Maxim Pontyushenko
Maxim Pontyushenko

Reputation: 3043

I guess You should make a range query between the start of the day and its ending(or without ending if you are talking about today). Something like this

db.collection.find({
  "date" : {"$gte": new Date("2015-07-07T00:00:00.000Z"),
            "$lt": new Date("2015-07-08T00:00:00.000Z")}
})

Upvotes: 42

Aphisith Keosavang
Aphisith Keosavang

Reputation: 61

This is my proposed solution:

db.collection.find({
  $and: [
  {"date": {$gte: new Date("2015-07-07T00:00:00.000Z")}},
  {"date": {$lt: new Date("2015-07-08T00:00:00.000Z")}}
  ]
})

Upvotes: 6

Nate Barr
Nate Barr

Reputation: 4660

I did a combination of the answers of Crash_Override and Maxim_PontyUshenko. Use Moment.js and the $gt, $lt mongo operators.

ship_date: {
  $lt: moment().hours(0).minutes(0).seconds(0).milliseconds(0).add(28, "days").toDate(),
  $gte: moment().hours(0).minutes(0).seconds(0).milliseconds(0).toDate()
}

Upvotes: 1

Related Questions