查询 Mongodb 日期时间的月、日、年...

发布于 2024-12-15 08:28:14 字数 429 浏览 1 评论 0原文

我正在使用 mongodb,并以这种方式将日期时间存储在我的数据库中,

用于存储日期“17-11-2011 18:00”:

date = datetime.datetime(2011, 11, 17, 18, 0)
db.mydatabase.mycollection.insert({"date" : date})

我想做这样的请求

month = 11
db.mydatabase.mycollection.find({"date.month" : month})

,或者

day = 17
db.mydatabase.mycollection.find({"date.day" : day})

有人知道如何执行此查询?

I'm using mongodb and I store datetime in my database in this way

for a date "17-11-2011 18:00" I store:

date = datetime.datetime(2011, 11, 17, 18, 0)
db.mydatabase.mycollection.insert({"date" : date})

I would like to do a request like that

month = 11
db.mydatabase.mycollection.find({"date.month" : month})

or

day = 17
db.mydatabase.mycollection.find({"date.day" : day})

anyone knows how to do this query?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(7

少钕鈤記 2024-12-22 08:28:14

日期以时间戳格式存储。如果您想要属于特定月份的所有内容,请查询该月的开始和结束。

var start = new Date(2010, 11, 1);
var end = new Date(2010, 11, 30);

db.posts.find({created_on: {$gte: start, $lt: end}});
//taken from http://cookbook.mongodb.org/patterns/date_range/

Dates are stored in their timestamp format. If you want everything that belongs to a specific month, query for the start and the end of the month.

var start = new Date(2010, 11, 1);
var end = new Date(2010, 11, 30);

db.posts.find({created_on: {$gte: start, $lt: end}});
//taken from http://cookbook.mongodb.org/patterns/date_range/
习惯成性 2024-12-22 08:28:14

您不能直接按日期组件(例如日或月)查询 mongodb 集合。但可以通过使用特殊的 $where javascript 表达式

db.mydatabase.mycollection.find({$where : function() { return this.date.getMonth() == 11} })

或简单地

db.mydatabase.mycollection.find({$where : 'return this.date.getMonth() == 11'})

(但我更喜欢第一个)

查看下面的 shell 命令来获取日期的部分

>date = ISODate("2011-09-25T10:12:34Z")
> date.getYear()
111
> date.getMonth()
8
> date.getdate()
25

编辑:

使用$where 仅当您别无选择时。它伴随着性能问题。请查看@kamaradclimber 和@dcrosta 的以下评论。我将公开这篇文章,以便其他人了解相关事实。

并查看链接 $where 查询中的子句和函数了解更多信息

You cannot straightly query mongodb collections by date components like day or month. But its possible by using the special $where javascript expression

db.mydatabase.mycollection.find({$where : function() { return this.date.getMonth() == 11} })

or simply

db.mydatabase.mycollection.find({$where : 'return this.date.getMonth() == 11'})

(But i prefer the first one)

Check out the below shell commands to get the parts of date

>date = ISODate("2011-09-25T10:12:34Z")
> date.getYear()
111
> date.getMonth()
8
> date.getdate()
25

EDIT:

Use $where only if you have no other choice. It comes with the performance problems. Please check out the below comments by @kamaradclimber and @dcrosta. I will let this post open so the other folks get the facts about it.

and check out the link $where Clauses and Functions in Queries for more info

萌酱 2024-12-22 08:28:14

使用 $expr 运算符允许在查询语言中使用聚合表达式。这将使您能够使用日期聚合运算符在您的查询中如下所示:

month = 11
db.mydatabase.mycollection.find({ 
    "$expr": { 
        "$eq": [ { "$month": "$date" }, month ] 
    } 
})

或者

day = 17
db.mydatabase.mycollection.find({ 
    "$expr": { 
        "$eq": [ { "$dayOfMonth": "$date" }, day ] 
    } 
})

您也可以使用 aggregate() 函数,接受 $redact 管道:

month = 11
db.mydatabase.mycollection.aggregate([
    {
        "$redact": {
            "$cond": [
                { "$eq": [ { "$month": "$date" }, month ] },
                "$KEEP",
                "$PRUNE"
            ]
        }
    }
])

对于其他请求

day = 17
db.mydatabase.mycollection.aggregate([
    {
        "$redact": {
            "$cond": [
                { "$eq": [ { "$dayOfMonth": "$date" }, day ] },
                "$KEEP",
                "$PRUNE"
            ]
        }
    }
])

使用 OR

month = 11
day = 17
db.mydatabase.mycollection.aggregate([
    {
        "$redact": {
            "$cond": [
                { 
                    "$or": [ 
                        { "$eq": [ { "$month": "$date" }, month ] },
                        { "$eq": [ { "$dayOfMonth": "$date" }, day ] }
                    ] 
                },
                "$KEEP",
                "$PRUNE"
            ]
        }
    }
])

使用 AND

var month = 11,
    day = 17;
db.collection.aggregate([
    {
        "$redact": {
            "$cond": [
                { 
                    "$and": [ 
                        { "$eq": [ { "$month": "$createdAt" }, month ] },
                        { "$eq": [ { "$dayOfMonth": "$createdAt" }, day ] }
                    ] 
                },
                "$KEEP",
                "$PRUNE"
            ]
        }
    }
])

$redact 运算符合并了$project 的功能$match 管道,并将使用 $$KEEP 并使用 $$PRUNE 变量。

Use the $expr operator which allows the use of aggregation expressions within the query language. This will give you the power to use the Date Aggregation Operators in your query as follows:

month = 11
db.mydatabase.mycollection.find({ 
    "$expr": { 
        "$eq": [ { "$month": "$date" }, month ] 
    } 
})

or

day = 17
db.mydatabase.mycollection.find({ 
    "$expr": { 
        "$eq": [ { "$dayOfMonth": "$date" }, day ] 
    } 
})

You could also run an aggregate operation with the aggregate() function that takes in a $redact pipeline:

month = 11
db.mydatabase.mycollection.aggregate([
    {
        "$redact": {
            "$cond": [
                { "$eq": [ { "$month": "$date" }, month ] },
                "$KEEP",
                "$PRUNE"
            ]
        }
    }
])

For the other request

day = 17
db.mydatabase.mycollection.aggregate([
    {
        "$redact": {
            "$cond": [
                { "$eq": [ { "$dayOfMonth": "$date" }, day ] },
                "$KEEP",
                "$PRUNE"
            ]
        }
    }
])

Using OR

month = 11
day = 17
db.mydatabase.mycollection.aggregate([
    {
        "$redact": {
            "$cond": [
                { 
                    "$or": [ 
                        { "$eq": [ { "$month": "$date" }, month ] },
                        { "$eq": [ { "$dayOfMonth": "$date" }, day ] }
                    ] 
                },
                "$KEEP",
                "$PRUNE"
            ]
        }
    }
])

Using AND

var month = 11,
    day = 17;
db.collection.aggregate([
    {
        "$redact": {
            "$cond": [
                { 
                    "$and": [ 
                        { "$eq": [ { "$month": "$createdAt" }, month ] },
                        { "$eq": [ { "$dayOfMonth": "$createdAt" }, day ] }
                    ] 
                },
                "$KEEP",
                "$PRUNE"
            ]
        }
    }
])

The $redact operator incorporates the functionality of $project and $match pipeline and will return all documents match the condition using $$KEEP and discard from the pipeline those that don't match using the $$PRUNE variable.

你是我的挚爱i 2024-12-22 08:28:14

既然您需要查询月份,那么将月份存储在它自己的属性中怎么样?不如 $where 优雅,但可能性能更好,因为它可以建立索引。

how about storing the month in its own property since you need to query for it? less elegant than $where, but likely to perform better since it can be indexed.

抽个烟儿 2024-12-22 08:28:14

如果您想搜索属于特定月份的文档,请确保像这样查询:

// Anything greater than this month and less than the next month
db.posts.find({created_on: {$gte: new Date(2015, 6, 1), $lt: new Date(2015, 7, 1)}});

尽可能避免像下面这样的查询。

// This may not find document with date as the last date of the month
db.posts.find({created_on: {$gte: new Date(2015, 6, 1), $lt: new Date(2015, 6, 30)}});

// don't do this too
db.posts.find({created_on: {$gte: new Date(2015, 6, 1), $lte: new Date(2015, 6, 30)}});

If you want to search for documents that belong to a specific month, make sure to query like this:

// Anything greater than this month and less than the next month
db.posts.find({created_on: {$gte: new Date(2015, 6, 1), $lt: new Date(2015, 7, 1)}});

Avoid quering like below as much as possible.

// This may not find document with date as the last date of the month
db.posts.find({created_on: {$gte: new Date(2015, 6, 1), $lt: new Date(2015, 6, 30)}});

// don't do this too
db.posts.find({created_on: {$gte: new Date(2015, 6, 1), $lte: new Date(2015, 6, 30)}});
猫性小仙女 2024-12-22 08:28:14

您可以通过日期聚合运算符查找按月、日、年等记录的日期,例如$dayOfYear、$dayOfWeek、$month、$year等。

例如您想要 2016 年 4 月创建的所有订单,您可以使用以下查询。

db.getCollection('orders').aggregate(
   [
     {
       $project:
         {
           doc: "$ROOT",
           year: { $year: "$created" },
           month: { $month: "$created" },
           day: { $dayOfMonth: "$created" }
         }
     },
     { $match : { "month" : 4, "year": 2016 } }
   ]
)

这里创建的是文档中的日期类型字段,$$ROOT 我们用来将所有其他字段传递给下一阶段的项目,并为我们提供文档的所有详细信息。

您可以根据需要优化上述查询,这只是举一个例子。要了解有关日期聚合运算符的更多信息,请访问链接

You can find record by month, day, year etc of dates by Date Aggregation Operators, like $dayOfYear, $dayOfWeek, $month, $year etc.

As an example if you want all the orders which are created in April 2016 you can use below query.

db.getCollection('orders').aggregate(
   [
     {
       $project:
         {
           doc: "$ROOT",
           year: { $year: "$created" },
           month: { $month: "$created" },
           day: { $dayOfMonth: "$created" }
         }
     },
     { $match : { "month" : 4, "year": 2016 } }
   ]
)

Here created is a date type field in documents, and $$ROOT we used to pass all other field to project in next stage, and give us all the detail of documents.

You can optimize above query as per your need, it is just to give an example. To know more about Date Aggregation Operators, visit the link.

绝對不後悔。 2024-12-22 08:28:14

您可以使用 MongoDB_DataObject 包装器来执行此类查询,如下所示:

$model = new MongoDB_DataObject('orders');

$model->whereAdd('MONTH(created) = 4 AND YEAR(created) = 2016');

$model->find();

while ($model->fetch()) {
    var_dump($model);
}

或者,类似地,使用直接查询字符串:

$model = new MongoDB_DataObject();

$model->query('SELECT * FROM orders WHERE MONTH(created) = 4 AND YEAR(created) = 2016');

while ($model->fetch()) {
    var_dump($model);
}

You can use MongoDB_DataObject wrapper to perform such query like below:

$model = new MongoDB_DataObject('orders');

$model->whereAdd('MONTH(created) = 4 AND YEAR(created) = 2016');

$model->find();

while ($model->fetch()) {
    var_dump($model);
}

OR, similarly, using direct query string:

$model = new MongoDB_DataObject();

$model->query('SELECT * FROM orders WHERE MONTH(created) = 4 AND YEAR(created) = 2016');

while ($model->fetch()) {
    var_dump($model);
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文