Linq filter by time range

Question

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

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 and tTo both are TimeSpan.

Any help? Regards

Edit: 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) :

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.

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);
}

Answer 1

Linq to SQL: how to query a time range in a DateTime field

If you are tring to select on a table the records that have a DateTime field (in this example called DateFiled) between two DateTime value, if you specify:

DateFiled >= dateValue && DateFiled <= dateValue

you select all the record between this value, but if you need to select only the records that between these two dates correspond to a specific time range, you’ll need to add a column that computes the hour.

If you do not want to add it to the database, you will need to add it to your model and set it with the same value as the DateField field. For example:

public async Task<List<MyModel>> GetFilteredResultByDateAndTime
  (DateTime startDate, DateTime endDate)
{
  var result = from mytable in _context.MyTable
               where
               mytable.DateField >= startDate.Date
               && mytable.DateField <= endDate.Date
               select new MyModel
               {
                 DateField = mytable.DateField.Date,
                 DateFieldTime = mytable.DateField,
                 // Other fields of the model
               };

  // Now you can filter the result by the time
  var filteredResult = from r in result
                       where 
                       r.DateFieldTime.TimeOfDay >= startDate.TimeOfDay
                       && r.DateFieldTime.TimeOfDay <= endDate.TimeOfDay
                       select r;
  return await filteredResult.ToListAsync();
}

Answer 2

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.

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

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);
        }

Edited: Note that it is not necessary to compare time with t2From and t1To because the comparison is always gonna be true but it makes the code easier to read because it explicitly checks that the date belongs to one of the two intervals.

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));

        }

Answer 3

ImportedOn.TimeOfDay

returns just a Time. For example:

            DateTime t = DateTime.Now;
            System.Diagnostics.Debug.WriteLine(t.ToString());
            var t2 = t.TimeOfDay;
            System.Diagnostics.Debug.WriteLine(t2.ToString());

returns:

02.06.2014 11:48:33
11:48:33.6671525

So you can just compare the Dates, there is no need of TimeOfDay.