Linq按时间范围过滤 [英] Linq filter by time range

问题描述

我正在尝试建立一个Linq查询，该查询每天进行两次过滤.

I'm trying to build a Linq query that filter between 2 times of day.

首先，我需要在日期之间进行过滤(即:2013年12月26日至2014年1月26日)，然后进行搜索，结果可能会在时间之间进行过滤(即:19:00至7之间的记录: 00).

First I need to filter between dates (ie.: 12/26/2013 to 01/26/2014) and after this search, the results may be filtered between times (ie.: records between 19:00 and 7:00).

我已经尝试过使用此查询，但是它不起作用:

I've tried with this query but it does not works:

orders = CurrentOrders.Where(o => o.ImportedOn >= dateFrom && o.ImportedOn <= dateTo);
orders = orders.Where(o => o.ImportedOn.TimeOfDay >= tFrom && o.ImportedOn.TimeOfDay <= tTo);

tFrom和tTo均为TimeSpan.

tFrom and tTo both are TimeSpan.

有帮助吗? 问候

您好，我正在编辑自己的问题，只是为了阐明问题并附上我发现的解决方案(我不确定它是否是最有效的):

Hi I'm editing my own question just to clarify the problem and attach a solution that I've found (I'm not sure if it's the most efficient) :

当我说不起作用"时，我的意思是没有返回符合条件的记录". 前一个查询仅适用于同一天之间的时间(即:从15:00到20:00).问题是时间跨度到第二天，例如从19:00 pm到7:00 am.

When I say "does not work" I mean "no records are returned that matched the criteria". The previous query works only for time between the same day (ie: from 15:00 to 20:00). The problem is when the time span to the next day, for example form 19:00pm to 7:00am.

我建议的解决方案是添加一个简单的检查是否tFrom小于tTo，否则在搜索中添加1到日期:

The solution that I propose is add a simple if to check if tFrom is less than tTo and otherwise add 1 to date in the search:

if (tFrom < tTo)
{
    orders = orders.Where(o => o.ImportedOn.TimeOfDay >= tFrom && o.ImportedOn.TimeOfDay <= tTo);
}
else
{
    orders = orders.Where(o => o.ImportedOn.TimeOfDay <= tFrom && o.ImportedOn.AddDays(1).TimeOfDay <= tTo);
}

推荐答案

例如，当时间范围为9 PM至4 AM时，我认为您的查询不会为您提供结果.假设ImportedOn是7 PM，并且您正在检查7 PM小于9 PM，这是可以的，并且还要检查7 PM是否小于4 AM，这是错误的.如果您增加一天的时间，我认为没有任何区别，因为您只是在考虑时间.添加日期不会更改时间.

I don't think your queries will give you results when the time range is 9 PM to 4 AM for example. Let's say that ImportedOn is 7 PM and you are checking that 7 PM is less than 9 PM, which is OK, and also checking that 7 PM is less than 4 AM, which is false. I don't see any difference if you add a day because you are only considering the time. Adding a date doesn't change the time.

我的建议是在从大于时间到(例如，从9 PM到4 AM)的时间之间创建两个时间间隔.

My proposal is to create two time intervals when the time from is greater than time to (9 PM to 4 AM for example).

我为DateTime创建了扩展方法，因此我们可以检查日期是否属于时间范围.

I created an extension method for DateTime so we can check if the date belongs to a time range.

public static bool IsInTimeRange(this DateTime obj, DateTime timeRangeFrom, DateTime timeRangeTo)
        {
            TimeSpan time = obj.TimeOfDay, t1From = timeRangeFrom.TimeOfDay, t1To = timeRangeTo.TimeOfDay;

            // if the time from is smaller than the time to, just filter by range
            if (t1From <= t1To)
            {
                return time >= t1From && time <= t1To;
            }

            // time from is greater than time to so two time intervals have to be created: one {timeFrom-12AM) and another one {12AM to timeTo}
            TimeSpan t2From = TimeSpan.MinValue, t2To = t1To;
            t1To = TimeSpan.MaxValue;

            return (time >= t1From && time <= t1To) || (time >= t2From && time <= t2To);
        }

已请注意，没有必要将时间与t2From和t1To进行比较，因为该比较总是正确的，但是由于它明确检查日期是否属于两者之一，因此它使代码更易于阅读.间隔.

我还写了这些单元测试:

I also wrote these unit tests:

[TestMethod]
        public void TimeRangeFilter_timeFrom_is_smaller_than_timeTo()
        {
            // arrange
            List<DateTime> dates = new List<DateTime>() 
            {
                DateTime.Today.AddHours(2), // 2 AM
                DateTime.Today.AddHours(9), // 9 AM
                DateTime.Today.AddHours(12), // 12 PM
                DateTime.Today.AddHours(15), // 3 PM
                DateTime.Today.AddHours(18), // 6 PM
                DateTime.Today.AddHours(23).AddMinutes(50), // 11:50 PM
                DateTime.Today, // 0 AM
            };
            // interval: 10 AM to 4 PM
            DateTime timeFrom = DateTime.Today.AddHours(10), timeTo = DateTime.Today.AddHours(16);

            // act
            var datesInPeriod = dates.Where(p => p.IsInTimeRange(timeFrom, timeTo));

            // assert
            Assert.IsFalse(datesInPeriod.Any(p => p.Hour == 2));
            Assert.IsFalse(datesInPeriod.Any(p => p.Hour == 9));
            Assert.IsTrue(datesInPeriod.Any(p => p.Hour == 12));
            Assert.IsTrue(datesInPeriod.Any(p => p.Hour == 15));
            Assert.IsFalse(datesInPeriod.Any(p => p.Hour == 18));
            Assert.IsFalse(datesInPeriod.Any(p => p.Hour == 23));

        }

        [TestMethod]
        public void TimeRangeFilter_timeFrom_is_greater_than_timeTo()
        {
            // arrange
            List<DateTime> dates = new List<DateTime>() 
            {
                DateTime.Today.AddHours(2), // 2 AM
                DateTime.Today.AddHours(9), // 9 AM
                DateTime.Today.AddHours(12), // 12 PM
                DateTime.Today.AddHours(15), // 3 PM
                DateTime.Today.AddHours(18), // 6 PM
                DateTime.Today.AddHours(23).AddMinutes(50), // 11:50 PM
                DateTime.Today, // 0 AM
            };
            // interval: 10 PM to 4 AM
            DateTime timeFrom = DateTime.Today.AddHours(22), timeTo = DateTime.Today.AddHours(4);

            // act
            var datesInPeriod = dates.Where(p => p.IsInTimeRange(timeFrom, timeTo));

            // assert
            Assert.IsTrue(datesInPeriod.Any(p => p.Hour == 2));
            Assert.IsFalse(datesInPeriod.Any(p => p.Hour == 9));
            Assert.IsFalse(datesInPeriod.Any(p => p.Hour == 12));
            Assert.IsFalse(datesInPeriod.Any(p => p.Hour == 15));
            Assert.IsFalse(datesInPeriod.Any(p => p.Hour == 18));
            Assert.IsTrue(datesInPeriod.Any(p => p.Hour == 23));

        }

这篇关于Linq按时间范围过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！