无法过滤以字符串形式存储在 Mongo DB 中的日期列

问题描述

我有一个将数据写入 MONGODB 的应用程序.
在文档中，我有一个名为 UpdatedOn 的字段.在这里，我以字符串格式编写日期时间，如下所示:

I have a application which writes data into MONGODB.
In the document, I have a field called UpdatedOn. In this I'm writing datetime in string format like below:

DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss")

我知道我应该只使用日期类型，现在它作为字符串存储在数据库中.
现在我需要根据这个字段在 2 个日期之间过滤数据.
像这样说:
开始日期:01/01/2019"
结束日期:31/01/2019"

I know I should have used date type only,this is being stored as string in the database now.
Now I got a requirement to filter data based on this field between 2 dates.
Say something like this :
Start Date : "01/01/2019"
End Date : "31/01/2019"

这是我在下面使用的代码(不起作用)

This is the code , I have used below (which is not working)

IMongoCollection<Order> OrderCollection = GetOrderCollection();
List<OrderFilter> lstJobs;

FilterDefinitionBuilder<Order> OrderFilter = Builders<Order>.Filter;

DateTime start = Convert.ToDateTime("01/01/2019");   
DateTime end = Convert.ToDateTime("31/01/2019");

var filter = OrderFilter.Gte("UpdatedOn", start) &
OrderFilter.Lt("UpdatedOn", end);

var fields = Builders<Order>.Projection.Include(p => p.Id);
lstOrders = await OrderCollection.Find(filter).Project<OrderFilter>(fields).ToListAsync<OrderFilter>().ConfigureAwait(false);

OrderFilter 类:

OrderFilter class:

public class OrderFilter
{    
   [DataMember(Name = "id")]
   public string Id { get; set; }
}

订单类:

public class Order
{
   [DataMember(Name = "id")]
   public string Id { get; set; }

   [DataMember(Name = "UpdatedOn")]
   public string UpdatedOn { get; set; }
}

因为 UpdatedOn 是字符串并且已经插入了一些数据.改变它是不切实际的.
在这种情况下，谁能帮我过滤.我可以在代码本身中进行任何类型转换或转换并进行过滤.
非常感谢！

Since UpdatedOn is string and already some data has been inserted. It is not practicle to change it.
Can anyone help me to filter in this case. Is there any typecasting or conversions I can do in the code itself and do the filtering.
Many thanks!

推荐答案

您必须将字符串解析为 Date 才能将其他日期与其进行比较.请参阅此处的文档.

You'll have to parse the string to Date to be able to compare other Dates to it.See the docs here.

为了能够在 C# 中执行此操作，您必须像这样使用 Aggregate 方法:

To be able to do this in C# you'll have to use the Aggregate method like this:

DateTime start = Convert.ToDateTime("01/01/2019");   
DateTime end = Convert.ToDateTime("31/01/2019");

var projectionDefinition = 
Builders<BsonDocument>.Projection.Exclude("convertedDate");

OrderCollection.Aggregate().AppendStage<BsonDocument>("{ $addFields: {convertedDate: { $toDate: '$UpdatedOn' }}}").Match(
    Builders<BsonDocument>.Filter.Gte(x => x["convertedDate"], new BsonDateTime(start))
    & Builders<BsonDocument>.Filter.Lte(x => x["convertedDate"], new BsonDateTime(end)))
    .Project(projectionDefinition).As<Order>();

您可能可以通过使用阶段使这更漂亮.

You'll probably be able to make this a lot prettier by using stages.

例如

var projectionDefinition = Builders<BsonDocument>.Projection.Exclude("convertedDate");
var expression = new BsonDocument(new List<BsonElement>
{
    new BsonElement("convertedDate", new BsonDocument(new BsonElement("$toDate", "$UpdatedOn")))
});

var addFieldsStage = new BsonDocument(new BsonElement("$addFields", expression));

var gteFilter = Builders<BsonDocument>.Filter.Gte(x => x["convertedDate"], new BsonDateTime(startDate));
var lteFilter = Builders<BsonDocument>.Filter.Lte(x => x["convertedDate"], new BsonDateTime(endDate));

var combinedFilter= Builders<BsonDocument>.Filter.And(gteFilter, lteFilter);

var result = coll.Aggregate().AppendStage<BsonDocument>(addFieldsStage).Match(combinedFilter).Project(projectionDefinition).As<Order>();

如果您在类上方添加 [BsonIgnoreExtraElements]，您将能够放弃投影阶段.

And if you add [BsonIgnoreExtraElements] above your class you'll be able to drop the projection stage.

或者在您的情况下，可能只是将其替换为您当前使用的投影并相应地设置 .As.

Or in your case probably just replace it with the projection you are currently using and setting the .As<Type> accordingly.

这篇关于无法过滤以字符串形式存储在 Mongo DB 中的日期列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！