MongoDB - 使用 C# 驱动程序按日期和时间搜索 [英] MongoDB - search by date and time using C# driver

问题描述

您好，我想使用 MongoDB 的 C# 驱动程序在两个日期(带时间)之间查找条目，但是我使用的 Find + Filter 方法忽略了时间并仅按日期搜索(我认为).我做错了什么?

我的 POCO:

公共类 TestClassForMongo{公共对象 ID { 获取；放;}公共日期时间 CreatedDateUtc { 获取；放;}公共字符串消息 { 获取；放;}}

我的搜索代码:

IMongoCollectioncollection = db.GetCollection("mongoTest");var filterBuilder = Builders.Filter;var filter = filterBuilder.Gt("CreatedDateUtc", new DateTime(2016, 03, 04, 21, 0, 0)) &filterBuilder.Lt("CreatedDateUtc", new DateTime(2016, 03, 04, 22, 0, 0));列表searchResult = collection.Find(filter).ToList();

上面的代码返回空数组，虽然这样:

collection.Find(filterBuilder.Empty).First().CreatedDateUtc

返回日期:2016-03-04 21:21:54"

MongoDB 3.2.3，C# MongoDB 驱动程序 2.2.3

驱动文档:

Hello I want to find entries between two dates (with time) using C# driver for MongoDB, but the Find + Filter method I use ignores the time and searches by date only (I think). What am I doing wrong?

My POCO:

public class TestClassForMongo
{
    public ObjectId Id { get; set; }
    public DateTime CreatedDateUtc { get; set; }
    public string Message { get; set; }
}

My search code:

IMongoCollection<TestClassForMongo> collection = db.GetCollection<TestClassForMongo>("mongoTest");

var filterBuilder = Builders<TestClassForMongo>.Filter;
var filter = filterBuilder.Gt("CreatedDateUtc", new DateTime(2016, 03, 04, 21, 0, 0)) &
             filterBuilder.Lt("CreatedDateUtc", new DateTime(2016, 03, 04, 22, 0, 0));
List<TestClassForMongo> searchResult = collection.Find(filter).ToList();

The above code returns empty array, although this:

collection.Find(filterBuilder.Empty).First().CreatedDateUtc

Returns the date: "2016-03-04 21:21:54"

MongoDB 3.2.3, C# MongoDB driver 2.2.3

The driver docs: https://docs.mongodb.org/getting-started/csharp/query/

THE ANSWER:

I did not give enough information for anyone to answer this question, the problem was timezone and UTC related issues, quite basic ones too. I used DateTime.UtcNow to store the date in the database. It is stored as "CreatedDateUtc" : ISODate("2016-03-04T21:21:54.836Z"). Getting this in C# returns a date which is actually a UTC date (the Kind property is UTC), which btw is indicated by the 'Z' suffix of the value in db. Comparing this UTC date with a new DateTime() makes not much sense, as the latter creates a date in your time zone, which can be different than +0 (UTC).

So one option would be to create the date for filter like this:

 new DateTime(2016, 03, 04, 21, 0, 0).ToUniversalTime()

Or modify the hour part to accomodate the timezone differences, in my example it would be adding 1 hour (as I am in +1 time zone).

So actually the time stored was 22:21:54 in my time zone. If I do a search between 22:00:00 and 23:00:00 using dates created in my time zone, I get the correct results.

解决方案

Add BSON attribute on dateTime field (see below),

you can use linqu syntax to build such a query

    var min = new DateTime(2016, 03, 03, 22, 0, 0);
    var max = (new DateTime(2016, 03, 03, 23, 0, 0));
    List<TestClassForMongo> searchResult = collection.Find( 
                x => x.CreatedDateUtc > min &
                x.CreatedDateUtc < max
                ).ToList();

BSON ATTRIBUTE

public class TestClassForMongo
{
    public ObjectId Id { get; set; }

    [BsonDateTimeOptions]
    public DateTime CreatedDateUtc { get; set; }

    public string Message { get; set; }
}

linqPad dump below:

这篇关于MongoDB - 使用 C# 驱动程序按日期和时间搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！