Robson Robson - 1 year ago 66
SQL Question

How to find the nearest date

I have the chart with three precisions.

Per hour,
Per 30 minutes,
Per 15 minutes.

Table with my data looks like this:

Data table

When i generating my chart i starting from the special date time for example from current date time

For example. When I starting from 18:00, and my preccision is per 15 minutes i need data from this times

  • 18:00

  • 17:45

  • 17:30

  • 17:15

  • 17:00

  • ...

In my data table I have data maximum per 3 minutes, so when i would like to get data from 17:15 my lambda query returns null because i have data only from 17:13 and 17:16.

So i need the query whitch return data nearest my data time. In upper example it's need to return data from 17:16.

I try DiffHours Method but it's don't work on MySQL. I need method working on MySQL and MSSQL

My current method looks like this:

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

but it's work only for per hour precision.

Thanks for help!


How about this, to get the closest time to a particular interval:

var fromTime = new DateTime(2016, 05, 20, 9, 0, 0);
var report = _reportRepository
             .OrderBy(m =>m.CreatedDate > fromTime 
                             ? m.CreatedDate - fromTime 
                             : fromTime - m.CreatedDate)