mongodb MongoDB只比较日期而没有时间

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/31272471/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 20:29:50  来源:igfitidea点击:

MongoDB comparing dates only without times

mongodb

提问by Crash Override

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 anytime, I want to match that day only with today's date.

如何仅使用日期而不是时间查询 MongoDB 中的日期字段?例如,如果 MongoDB 存储 2015 年 7 月 7 日的任何时间,我只想将那天与今天的日期匹配。

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

在 MySQL 中,我会做 SELECT col1, col2 FROM table WHERE DATE(date_field) = DATE(NOW());

Notice how MySQL has a function to change the date_fieldto 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?

请注意 MySQL 如何具有仅在匹配期间将date_field更改为日期的功能。我可以在匹配过程中使用类似的功能在 MongoDB 中执行此操作吗?像Collection.find({ dateonly(datetimefield): dateonly(new Date() });或类似的东西?

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.

我了解 Mongo 在 GMT 中存储日期/时间,因此我还必须在仅匹配日期之前将存储的 GMT 日期 + 时间从 GMT 转换为本地,但为了让我开始,想知道我是否可以去掉时间日期匹配。例如,如果 GMT 现在是 7 月 8 日 03:00,但美国东部时间是 7 月 7 日 23:00,那么我想匹配 7 月 7 日。

采纳答案by Crash Override

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

2018 年 6 月 26 日更新,修复了使用 moment() 而不是 new Date() 的代码

So it sounds like there is no mongodb equivalent to MySQL's DATEfunction, like with SELECT col1, col2 FROM table WHERE DATE(date_field) = DATE(NOW())

所以听起来没有 mongodb 相当于 MySQL 的DATE功能,就像SELECT col1, col2 FROM table WHERE DATE(date_field) = DATE(NOW())

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

代替本机解决方案,我通过使用 MomentJS Timezone ( http://momentjs.com/timezone/) 将日期/时间转换为仅限日期的数字字段来解决此问题,然后将日期存储为数字。

In my javascript code (outside of MongoDB):

在我的 javascript 代码中(在 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.

然后我在 Mongo 记录中存储一个仅限日期的字段。

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.

然后在我的 Javascript 代码中,我可以轻松查询今天、明天、特定日期等。

// 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
// regardless which time zone they are in
// just find the date in YYYYMMDD format
db.birthdays.find( { dateonly: 20150715 } );

Hope this helps someone. It's storing a number, not a string, on purpose to improve performance on its index. I have other code which enforces a valid-looking number, before storing into the database it checks:

希望这可以帮助某人。它存储一个数字,而不是一个字符串,目的是提高其索引的性能。我还有其他代码可以强制执行一个有效的数字,在存储到数据库之前它会检查:

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

回答by Maxim Pontyushenko

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")}
})

回答by Atikur Rahman Sabuj

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}}

回答by Aphisith Keosavang

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")}}
  ]
})

回答by Nate Barr

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

我结合了 Crash_Override 和 Maxim_PontyUshenko 的答案。使用 Moment.js 和 $gt, $lt mongo 操作符。

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()
}