Geek Answers Handbook

How to find the nearest date

I have a chart with three corrections.

Per hour, After 30 minutes, Within 15 minutes.

The table with my data is as follows:

Data table

When I create my chart i, starting from a special date time, for example, from the current time

For instance. When I start at 18:00, and my preliminary exam in 15 minutes, I need data from this time.

  • 18:00
  • 17:45
  • 17:30
  • 17:15
  • 5 p.m.
  • ...

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

Therefore, I need the whitch request data to return closer to my time. In the upper example, he needs to return the data from 17:16.

DiffHours Method, MySQL. MySQL MSSQL

:

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

.

!

+4
2

, :

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

, :

  • , , , . 18:00, 18:15, 18:30 15- ( ), fromTime

  • , , , 18:00, 17:48

  • ,

  • , . 15- , 17:45:00 18:14:59, 18:00 ( 3 , , )

. 15 ( 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)) ;

( 7:30 ​​15 , 30- :

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

( 30:00 60 ).

CreatedDate 15/30/60 . , CreatedDate 2016-05-20 09:15:00 2016-05-20 09:30:00 30- , 2016-05-20 09:00:00.

, , ; , . 2016-05-20 09:18:40, 2016-05-20 09:15:00, 2016-05-20 09:30:00 2016-05-20 09:00:00 15 , 30 1 . (3: 40 , 11: 20 18: 40 ).

filtertime CreatedDate, 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();
+1

More articles:


All Articles