How to find the nearest date

c# entity-framework-6 lambda mysql sql-server

Question

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!

1
4
5/20/2016 10:06:32 AM

Accepted Answer

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)
             .Take(1);
3
5/20/2016 2:52:30 PM

Popular Answer

You showed only limited code and weren't utterly specific about some points even after several questions about it, so I will assume the following:

  • you are able to create a working report for hourly precision, that means you are able to generate the list of desired times, e.g. 18:00, 18:15, 18:30 for a 15 minute interval (you just can't get the correct data for it) and these times are in the variable fromTime

  • you always have round times as report times, so e.g. 18:00, never 17:48

  • your closest entries can be both before and after the query time

  • if you do e.g. a 15 minutes report, and there is no value in your database with a datetime between 17:45:00 and 18:14:59, the report will not have any result for 18:00 (since your data covers every 3 minutes, it shouldn't be a problem anyway, except for pauses)

You have to use different queries for the 3 interval times. For 15 minutes use (assuming your table is called a):

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

For the other intervals, you have to replace the interval accordingly (so replace 7:30 by half the interval time in minutes, and 15 by the interval time in minutes, so for 30 minute interval 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 interval).

This will basically round your CreatedDate to the closest whole 15/30/60 minutes and order it by the timedifference to that. It will always round up, so a CreatedDate 2016-05-20 09:15:00 will be rounded to 2016-05-20 09:30:00 for a 30 minute interval, not to 2016-05-20 09:00:00.

You might want to take a direct look at the result to understand the final view; for your example data, e.g. 2016-05-20 09:18:40, it will first calculate the filtertimes 2016-05-20 09:15:00, 2016-05-20 09:30:00 and 2016-05-20 09:00:00 for the 3 queries 15 minutes, 30 minutes and 1 hour. It will then order it according to their distance to these times (3:40min, 11:20min and 18:40min).

Your reportfilter will have to use the filtertime instead of the CreatedDate time, and you have to add the minutes to compare to 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();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow