使用mongoDB c#驱动程序仅按日期过滤 [英] Filter only by Date using mongoDB c# driver

问题描述

我在项目中使用的是mongoDB c#最新驱动程序，即3. +.通过使用daterangepicker，我有不同的日期过滤条件，例如今天，过去一天，昨天，本月等.

这是我的模特

public class Student
    {
        public Student()
        {
        }
        [BsonId]
        [BsonRepresentation(BsonType.ObjectId)]
        public string Id { get; set; }
        [BsonDateTimeOptions(Kind = DateTimeKind.Local)]
        public DateTime CreatedOn { get; set; }
        [BsonDateTimeOptions(Kind = DateTimeKind.Local)]
        public DateTime ModifiedOn { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
    }

这是驱动程序代码

var server = new MongoClient(_connectionString);
var db = server.GetDatabase("Students");
var collection = db.GetCollection<Student>("student");
var filterBuilder = Builders<Student>.Filter;
var start = new DateTime(2017, 03, 29);
var end = new DateTime(2017, 03, 31);
var filter = filterBuilder.Gte(x => x.CreatedOn, new BsonDateTime(start)) &
             filterBuilder.Lte(x => x.CreatedOn, new BsonDateTime(end));
List<Student> searchResult = collection.Find(filter).ToList();

此代码可以正常工作，但是当我选择今天"过滤器时，日期将变为

var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 03, 31);

它没有返回当天的记录.它也在计算时间.

我将日期另存为DateTime.Now.我要查询的样本ISO日期是

"CreatedOn": ISODate("2017-03-31T20:27:12.914+05:00"),
"ModifiedOn": ISODate("2017-03-31T20:27:12.914+05:00"),

这是我正在使用的日期过滤器.我应该从结束日期减去-1吗?

需要帮助我做错了事.

解决方案

我相信您会对时区(尤其是偏移量部分)感到困惑.

MongoDb始终以UTC时间保存日期.

因此，当您在MongoDB中查看日期时间时，总是必须考虑与本地时区的偏移量.

您将始终在本地时区发送日期.在持续之前，Mongo C#驱动程序将时间从本地更改为UTC.

例如

当我使用CreatedOn = 2017-04-05 15:21:23.234(当地时区(美国/芝加哥))保存文档时，但是 当您查看数据库中的文档时，您会看到ISODate("2017-04-05T20:21:23.234Z")的内容，即本地时间与UTC的时差为-5小时.

[BsonDateTimeOptions(Kind = DateTimeKind.Local)]指示驱动程序，在将BSON序列化为POCO时将时间从UTC转换为本地时间.

这是说明行为的测试用例.

代码:

class Program
{

    static void Main(string[] args)
    {
        var mongo = new MongoClient("mongodb://localhost:27017/test");
        var db = mongo.GetDatabase("test");

        db.DropCollection("students");
        db.CreateCollection("students");

        var collection = db.GetCollection<Student>("students");

        var today = DateTime.Now; //2017-04-05 15:21:23.234
        var yesterday = today.AddDays(-1);//2017-04-04 15:21:23.234

        // Create 2 documents (yesterday &  today)
        collection.InsertMany(new[]
            {
            new Student{Description = "today", CreatedOn = today},
            new Student{Description = "yesterday", CreatedOn = yesterday},
            }
         );

        var filterBuilder1 = Builders<Student>.Filter;
        var filter1 = filterBuilder1.Eq(x => x.CreatedOn, today);
        List<Student> searchResult1 = collection.Find(filter1).ToList();

        Console.Write(searchResult1.Count == 1);

        var filterBuilder2 = Builders<Student>.Filter;
        var filter2 = filterBuilder2.Eq(x => x.CreatedOn, yesterday);
        List<Student> searchResult2 = collection.Find(filter2).ToList();

        Console.Write(searchResult2.Count == 1);

    }
}

public class Student
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id { get; set; }
    [BsonDateTimeOptions(Kind = DateTimeKind.Local)]
    public DateTime CreatedOn { get; set; }
    public string Description { get; set; }
}

收藏夹:(通过mongo shell进行查看时)

{
        "_id" : ObjectId("58e559c76d3a9d2cb0449d84"),
        "CreatedOn" : ISODate("2017-04-04T20:21:23.234Z"),
        "Description" : "yesterday"
}
{
        "_id" : ObjectId("58e559c76d3a9d2cb0449d85"),
        "CreatedOn" : ISODate("2017-04-05T20:21:23.234Z"),
        "Description" : "today"
}

更新:

"CreatedOn": ISODate("2017-03-31T20:27:12.914+05:00")

您的比较不起作用的原因是

 var start = new DateTime(2017, 03, 31);
 var end = new DateTime(2017, 03, 31);

此文件以与ISODate("2017-03-31T00:00:00.000+05:00")相同的$gte和与ISODate("2017-03-31T00:00:00.000+05:00")相同的$lte的形式发送到服务器，并且找不到上述条目.

查询today日期的正确方法是

 var start = new DateTime(2017, 03, 31);
 var end = new DateTime(2017, 04, 01);

并将您的过滤器更新为

var filter = filterBuilder.Gte(x => x.CreatedOn, start) &
         filterBuilder.Lt(x => x.CreatedOn, end);

因此，现在您的范围查询以$gte而不是ISODate("2017-03-31T00:00:00.000+05:00")和$lt而不是ISODate("2017-04-01T00:00:00.000+05:00")的形式发送到服务器，并且您应该能够找到今天的所有匹配项.

更新2

更改数据库以存储日期时间，并将时间部分设置为00:00:00.这样也会从db中删除方程式中的时间部分，并且您的旧范围查询在所有情况下都可以正常工作.

更改保存方式以使用

var today = DateTime.Today; //2017-03-31 00:00:00.000

您可以返回到旧的过滤器定义.

类似

 var start = new DateTime(2017, 03, 31);
 var end = new DateTime(2017, 03, 31);

并将您的过滤器更新为

var filter = filterBuilder.Gte(x => x.CreatedOn, start) &
         filterBuilder.Lte(x => x.CreatedOn, end);

因此，现在您的范围查询以$gte而不是ISODate("2017-03-31T00:00:00.000+05:00")和$lte而不是ISODate("2017-03-31T00:00:00.000+05:00")的形式发送到服务器，并且您应该能够找到今天的所有匹配项.

更新3 -使用BsonDocument仅日期比较.

这里的想法是将时区偏移量(+5:00)添加到服务器的UTC日期，并使用$dateToSting运算符将计算出的日期时间转换为字符串yyyy-MM-dd格式，然后比较相同格式的输入字符串日期. /p>

这将在您的时区中起作用，但在DST的时区中不能起作用.

Mongo版本3.4

您可以使用$addFields阶段，该阶段会添加新字段CreatedOnDate，同时保留所有现有属性，最后一个$project会将CreatedOnDate从比较后的最终响应中删除.

Shell查询:

{
    "$addFields": {
        "CreatedOnDate": {
            "$dateToString": {
                "format": "%Y-%m-%d",
                "date": {
                    "$add": ["$CreatedOn", 18000000]
                }
            }
        }
    }
}, {
    "$match": {
        "CreatedOnDate": {
            "$gte": "2017-03-31",
            "$lte": "2017-03-31"
        }
    }
}, {
    "$project": {
        "CreatedOnDate": 0
    }
}

C#代码:

var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 03, 31);

var addFields = BsonDocument.Parse("{$addFields: { CreatedOnDate: { $dateToString: { format: '%Y-%m-%d', date: {$add: ['$CreatedOn', 18000000] }} }} }");

var match = new BsonDocument("CreatedOnDate", new BsonDocument("$gte", start.ToString("yyyy-MM-dd")).Add("$lte", end.ToString("yyyy-MM-dd")));

var project = new BsonDocument
     {
       { "CreatedOnDate", 0 }
     };

var pipeline = collection.Aggregate().AppendStage<BsonDocument>(addFields)
    .Match(match)
    .Project(project);

var list = pipeline.ToList();

List<Student> searchResult = list.Select(doc => BsonSerializer.Deserialize<Student>(doc)).ToList();

Mongo版本= 3.2

与上述相同，但该管道使用$project，因此您必须添加要保留在最终响应中的所有字段.

Shell查询:

{
    "$project": {
        "CreatedOn": 1,
        "Description": 1,
        "CreatedOnDate": {
            "$dateToString": {
                "format": "%Y-%m-%d",
                "date": {
                    "$add": ["$CreatedOn", 18000000]
                }
            }
        }
    }
}, {
    "$match": {
        "CreatedOnDate": {
            "$gte": "2017-03-31",
            "$lte": "2017-03-31"
        }
    }
}, {
    "$project": {
        "CreatedOn": 1,
        "Description": 1
    }
}

C#代码:

var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 03, 31);

var project1 = new BsonDocument
    {
        { "CreatedOn", 1 },
        { "Description", 1 },
        { "CreatedOnDate", new BsonDocument("$dateToString", new BsonDocument("format", "%Y-%m-%d")
                            .Add("date", new BsonDocument("$add", new BsonArray(new object[] { "$CreatedOn", 5 * 60 * 60 * 1000 }))))
        }
    };

var match = new BsonDocument("CreatedOnDate", new BsonDocument("$gte", start.ToString("yyyy-MM-dd")).Add("$lte", end.ToString("yyyy-MM-dd")));

var project2 = new BsonDocument
    {
        { "CreatedOn", 1 },
        { "Description", 1 }
    };


var pipeline = collection.Aggregate()
.Project(project1)
.Match(match)
.Project(project2);

var list = pipeline.ToList();

List<Student> searchResult = list.Select(doc => BsonSerializer.Deserialize<Student>(doc)).ToList();

更新4 -适用于夏令时的仅日期比较.

Mongo版本= 3.6

一切保持不变，期望$dateToString将采用时区而不是固定的偏移量，这应考虑日光节约时间的变化.

Shell更新:

{
    "$addFields": {
        "CreatedOnDate": {
            "$dateToString": {
                "format": "%Y-%m-%d",
                "date": "$CreatedOn",
                "timezone": "America/New_York"
            }
        }
    }
}

C#更新:

var addFields = BsonDocument.Parse("{$addFields: { CreatedOnDate: { $dateToString: { format: '%Y-%m-%d', date: "$CreatedOn", "timezone": "America/New_York"} }} }");

I am using mongoDB c# latest driver i.e 3.+ in my project. I have different date filter criteria like Today,Last Day,Yesterday,This Month etc by using daterangepicker.

Here is my model

public class Student
    {
        public Student()
        {
        }
        [BsonId]
        [BsonRepresentation(BsonType.ObjectId)]
        public string Id { get; set; }
        [BsonDateTimeOptions(Kind = DateTimeKind.Local)]
        public DateTime CreatedOn { get; set; }
        [BsonDateTimeOptions(Kind = DateTimeKind.Local)]
        public DateTime ModifiedOn { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
    }

Here is the driver code

var server = new MongoClient(_connectionString);
var db = server.GetDatabase("Students");
var collection = db.GetCollection<Student>("student");
var filterBuilder = Builders<Student>.Filter;
var start = new DateTime(2017, 03, 29);
var end = new DateTime(2017, 03, 31);
var filter = filterBuilder.Gte(x => x.CreatedOn, new BsonDateTime(start)) &
             filterBuilder.Lte(x => x.CreatedOn, new BsonDateTime(end));
List<Student> searchResult = collection.Find(filter).ToList();

This code works fine but when I select todays filter then the date becomes

var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 03, 31);

It didn't returns records for the current day . It is calculating time also.

I am saving dates as DateTime.Now. Sample ISO Date which I am querying are

"CreatedOn": ISODate("2017-03-31T20:27:12.914+05:00"),
"ModifiedOn": ISODate("2017-03-31T20:27:12.914+05:00"),

This is the dates filter I am using . Should I have to subract -1 from end dates ?

Need help what I am doing wrong.

解决方案

I believe you are getting confused with time zones especially the offset part.

MongoDb always saves the date in UTC time.

So when you look at the date time in MongoDB you always have to factored in offset from your local time zone.

You'll always send the date in local time zone. Mongo C# driver changes time from local to UTC before persisting.

For example

When I save the document with CreatedOn = 2017-04-05 15:21:23.234 ( local time zone (America/Chicago) ) but when you look at the documents in DB you will see something ISODate("2017-04-05T20:21:23.234Z") i.e local time offset from UTC which is -5 hours.

[BsonDateTimeOptions(Kind = DateTimeKind.Local)] indicates to driver to convert the time to local time from UTC when deserailsing the BSON back to your POCO.

Here is the test case explaining the behavior.

Code :

class Program
{

    static void Main(string[] args)
    {
        var mongo = new MongoClient("mongodb://localhost:27017/test");
        var db = mongo.GetDatabase("test");

        db.DropCollection("students");
        db.CreateCollection("students");

        var collection = db.GetCollection<Student>("students");

        var today = DateTime.Now; //2017-04-05 15:21:23.234
        var yesterday = today.AddDays(-1);//2017-04-04 15:21:23.234

        // Create 2 documents (yesterday &  today)
        collection.InsertMany(new[]
            {
            new Student{Description = "today", CreatedOn = today},
            new Student{Description = "yesterday", CreatedOn = yesterday},
            }
         );

        var filterBuilder1 = Builders<Student>.Filter;
        var filter1 = filterBuilder1.Eq(x => x.CreatedOn, today);
        List<Student> searchResult1 = collection.Find(filter1).ToList();

        Console.Write(searchResult1.Count == 1);

        var filterBuilder2 = Builders<Student>.Filter;
        var filter2 = filterBuilder2.Eq(x => x.CreatedOn, yesterday);
        List<Student> searchResult2 = collection.Find(filter2).ToList();

        Console.Write(searchResult2.Count == 1);

    }
}

public class Student
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id { get; set; }
    [BsonDateTimeOptions(Kind = DateTimeKind.Local)]
    public DateTime CreatedOn { get; set; }
    public string Description { get; set; }
}

Collection : (when viewed through mongo shell)

{
        "_id" : ObjectId("58e559c76d3a9d2cb0449d84"),
        "CreatedOn" : ISODate("2017-04-04T20:21:23.234Z"),
        "Description" : "yesterday"
}
{
        "_id" : ObjectId("58e559c76d3a9d2cb0449d85"),
        "CreatedOn" : ISODate("2017-04-05T20:21:23.234Z"),
        "Description" : "today"
}

Update :

"CreatedOn": ISODate("2017-03-31T20:27:12.914+05:00")

The reason your comparison is not working is

 var start = new DateTime(2017, 03, 31);
 var end = new DateTime(2017, 03, 31);

This gets send to server as as $gte than ISODate("2017-03-31T00:00:00.000+05:00") and $lte than ISODate("2017-03-31T00:00:00.000+05:00") and it doesnt find the above entry.

The right way to query for today date will be

 var start = new DateTime(2017, 03, 31);
 var end = new DateTime(2017, 04, 01);

and update your filter to

var filter = filterBuilder.Gte(x => x.CreatedOn, start) &
         filterBuilder.Lt(x => x.CreatedOn, end);

So now your range query is send to server as $gte than ISODate("2017-03-31T00:00:00.000+05:00") and $lt than ISODate("2017-04-01T00:00:00.000+05:00") and you should be able to find all matches for today.

Update 2

Change your database to store the date time with time part set to 00:00:00. This will remove the time part out of the equation from db too and your old range queries will work just fine for all cases.

Change your save method to use

var today = DateTime.Today; //2017-03-31 00:00:00.000

You can go back to old filter definition.

Something like

 var start = new DateTime(2017, 03, 31);
 var end = new DateTime(2017, 03, 31);

and update your filter to

var filter = filterBuilder.Gte(x => x.CreatedOn, start) &
         filterBuilder.Lte(x => x.CreatedOn, end);

So now your range query is send to server as $gte than ISODate("2017-03-31T00:00:00.000+05:00") and $lte than ISODate("2017-03-31T00:00:00.000+05:00") and you should be able to find all matches for today.

Update 3 - Date only comparison using BsonDocument.

The idea here is to add timezone offset which is +5:00 to the server's UTC date and transform the calculated datetime to string yyyy-MM-dd format using $dateToSting operator followed by comparison on input string date in the same format.

This will work in your timezone but will not work in DST observing time zones.

Mongo Version 3.4

You can use $addFields stage which adds new field CreatedOnDatewhile keeping all the existing properties and last $project to drop the CreatedOnDate from the final response after comparison.

Shell Query:

{
    "$addFields": {
        "CreatedOnDate": {
            "$dateToString": {
                "format": "%Y-%m-%d",
                "date": {
                    "$add": ["$CreatedOn", 18000000]
                }
            }
        }
    }
}, {
    "$match": {
        "CreatedOnDate": {
            "$gte": "2017-03-31",
            "$lte": "2017-03-31"
        }
    }
}, {
    "$project": {
        "CreatedOnDate": 0
    }
}

C# code :

var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 03, 31);

var addFields = BsonDocument.Parse("{$addFields: { CreatedOnDate: { $dateToString: { format: '%Y-%m-%d', date: {$add: ['$CreatedOn', 18000000] }} }} }");

var match = new BsonDocument("CreatedOnDate", new BsonDocument("$gte", start.ToString("yyyy-MM-dd")).Add("$lte", end.ToString("yyyy-MM-dd")));

var project = new BsonDocument
     {
       { "CreatedOnDate", 0 }
     };

var pipeline = collection.Aggregate().AppendStage<BsonDocument>(addFields)
    .Match(match)
    .Project(project);

var list = pipeline.ToList();

List<Student> searchResult = list.Select(doc => BsonSerializer.Deserialize<Student>(doc)).ToList();

Mongo Version = 3.2

Same as above but this pipeline uses $project so you'll have to add all the fields that you want to keep in final response.

Shell Query:

{
    "$project": {
        "CreatedOn": 1,
        "Description": 1,
        "CreatedOnDate": {
            "$dateToString": {
                "format": "%Y-%m-%d",
                "date": {
                    "$add": ["$CreatedOn", 18000000]
                }
            }
        }
    }
}, {
    "$match": {
        "CreatedOnDate": {
            "$gte": "2017-03-31",
            "$lte": "2017-03-31"
        }
    }
}, {
    "$project": {
        "CreatedOn": 1,
        "Description": 1
    }
}

C# code:

var start = new DateTime(2017, 03, 31);
var end = new DateTime(2017, 03, 31);

var project1 = new BsonDocument
    {
        { "CreatedOn", 1 },
        { "Description", 1 },
        { "CreatedOnDate", new BsonDocument("$dateToString", new BsonDocument("format", "%Y-%m-%d")
                            .Add("date", new BsonDocument("$add", new BsonArray(new object[] { "$CreatedOn", 5 * 60 * 60 * 1000 }))))
        }
    };

var match = new BsonDocument("CreatedOnDate", new BsonDocument("$gte", start.ToString("yyyy-MM-dd")).Add("$lte", end.ToString("yyyy-MM-dd")));

var project2 = new BsonDocument
    {
        { "CreatedOn", 1 },
        { "Description", 1 }
    };


var pipeline = collection.Aggregate()
.Project(project1)
.Match(match)
.Project(project2);

var list = pipeline.ToList();

List<Student> searchResult = list.Select(doc => BsonSerializer.Deserialize<Student>(doc)).ToList();

Update 4 - Date only comparison that works with day light savings.

Mongo Version = 3.6

Everything stays same expect $dateToString will take the timezone instead of fixed offset which should take care of day light saving changes into account.

Shell Update:

{
    "$addFields": {
        "CreatedOnDate": {
            "$dateToString": {
                "format": "%Y-%m-%d",
                "date": "$CreatedOn",
                "timezone": "America/New_York"
            }
        }
    }
}

C# Update:

var addFields = BsonDocument.Parse("{$addFields: { CreatedOnDate: { $dateToString: { format: '%Y-%m-%d', date: "$CreatedOn", "timezone": "America/New_York"} }} }");

这篇关于使用mongoDB c#驱动程序仅按日期过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！