How to find the nearest date

I'm going to presume the following because you only displayed a little amount of code and weren't quite clear on several issues despite my repeated questions:

• You can build the list of desired times, such as 18:00, 18:15, and 18:30 for a 15-minute interval, and these times are in the variable. This implies that you can create a workable report for hourly precision.fromTime

• You never use 17:48 as a report time; instead, you always use round numbers, like 18:00.

• Both before and after the query time, your closest entries may exist.

• Since your data covers every three minutes, there shouldn't be a problem, other than pauses, if you run a 15 minutes report and there is no information in your database with a datetime between 17:45:00 and 18:14:59.

For each of the three interval times, you must use a new query. Use for 15 minutes, provided your table is calleda ):

select *
from
(select *,
 convert(timestamp(date(date_add(CreatedDate, INTERVAL '7:30' MINUTE_SECOND)), 
         maketime(hour(date_add(CreatedDate, INTERVAL '7:30' MINUTE_SECOND)), 
         round(minute(date_add(CreatedDate, INTERVAL '7:30' MINUTE_SECOND)) div 15) 
          * 15, 0)), datetime) as filtertime
 from a
) as withfilter
order by filtertime, abs(timediff(filtertime, CreatedDate)) ;

You must replace the interval in accordance with the other intervals (thus replace7:30 by halving the interval's minute duration, and15 based on the interval's length in minutes; for example, for a 30-minute period, it would be:

select *
from
(select *,
 convert(timestamp(date(date_add(CreatedDate, INTERVAL '15:00' MINUTE_SECOND)), 
         maketime(hour(date_add(CreatedDate, INTERVAL '15:00' MINUTE_SECOND)), 
         round(minute(date_add(CreatedDate, INTERVAL '15:00' MINUTE_SECOND)) div 30) 
          * 30, 0)), datetime) as filtertime
 from a
) as withfilter
order by filtertime, abs(timediff(filtertime, CreatedDate)) ;

(and 30:00 and 60 for the hourly pause).

Essentially, this will round yourCreatedDate the time difference to the nearest whole 15/30/60 minutes, and then sort it according to that. It will round up constantly, so aCreatedDate2016-05-20 09:15:00 being rounded to2016-05-20 09:30:00 not to exceed 30 minutes at a time2016-05-20 09:00:00 .

To grasp the final view, you might want to take a close look at the outcome; for your example data, for instance.2016-05-20 09:18:40 it first determines the filtertimes.2016-05-20 09:15:00 , 2016-05-20 09:30:00 and 2016-05-20 09:00:00 15 minutes, 30 minutes, and an hour were spent on the three requests. Then, it will arrange it in accordance with their proximity to these times (3:40min, 11:20min and 18:40min).

The reportfilter you use must make use of thefiltertime as opposed to theCreatedDate time, and in order to compare to it, you must add the minutes.fromtime :

var report = _reportRepository.FindBy(a => a.Fridge.FridgeIdentity == fridgeIdentity 
   && a.filtertime.Year == fromTime.Year 
   && a.filtertime.Month == fromTime.Month 
   && a.filtertime.Day == fromTime.Day 
   && a.filtertime.Hour == fromTime.Hour
   && a.filtertime.Minute == fromTime.Minute).FirstOrDefault();