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
MongoDB comparing dates only without times
提问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 DATE
function, 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()
}