I have list of object like this one:
+-------------+------------+---------------------+
| ID | Value | Time |
+-------------+------------+---------------------+
| 1 | 1 | 2019-03-07 20:05:35 |
| 2 | 2 | 2019-03-07 20:06:09 |
| 5 | 5 | 2019-03-07 20:11:27 |
| 7 | 1 | 2019-03-07 20:13:30 |
| 8 | 0 | 2019-03-07 20:13:41 |
| 7 | 1 | 2019-03-07 20:17:00 |
| 8 | 0 | 2019-03-07 20:22:20 |
| 7 | 1 | 2019-03-07 20:23:05 |
| 8 | 0 | 2019-03-07 20:27:35 |
| 7 | 1 | 2019-03-07 20:27:37 |
| 8 | 0 | 2019-03-07 20:28:01 |
| 7 | 1 | 2019-03-07 20:37:19 |
| 8 | 0 | 2019-03-07 20:37:27 |
| 7 | 1 | 2019-03-07 20:37:54 |
| 8 | 0 | 2019-03-07 20:40:11 |
| 7 | 1 | 2019-03-07 20:44:00 |
| 8 | 0 | 2019-03-07 20:45:00 |
| 7 | 1 | 2019-03-07 20:47:41 |
| 7 | 1 | 2019-03-07 20:48:43 |
| 7 | 1 | 2019-03-07 20:48:51 |
| 8 | 0 | 2019-03-07 20:51:11 |
| 8 | 0 | 2019-03-07 20:54:46 |
| 8 | 0 | 2019-03-07 20:55:36 |
+-------------+------------+---------------------+
How to select records 15 minutes apart but records that are more close to the next time?
The result should be something like this:
+-------------+------------+---------------------+
| ID | Value | Time |
+-------------+------------+---------------------+
| 1 | 1 | 2019-03-07 20:05:35 |
| 8 | 0 | 2019-03-07 20:22:20 |
| 7 | 1 | 2019-03-07 20:37:19 |
| 8 | 0 | 2019-03-07 20:51:11 |
+-------------+------------+---------------------+
If the first time is 20:05:35 and next apart 15 min is 20:22:20. The closest time to that is 20:22:20 because difference between them is 00:02:14 and between 20:17:35 is difference 00:02:25. Is there any way to calculate difference and make decision which one is closer to choose?
Try following :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
namespace ConsoleApplication132
{
class Program
{
static void Main(string[] args)
{
List<Dates> dates = Dates.GetDates();
var results = dates.GroupBy(x => new DateTime(x.Time.Year, x.Time.Month, x.Time.Day, x.Time.Hour, 15 * (x.Time.Minute / 15), 0).AddMinutes(15))
.Select(x => x.OrderBy(y => x.Key.Subtract(y.Time)).First())
.ToList();
}
}
public class Dates
{
public int ID { get;set;}
public int Value { get;set;}
public DateTime Time { get;set;}
public static List<Dates> GetDates()
{
string input =
"| 1 | 1 | 2019-03-07 20:05:35 |\n" +
"| 2 | 2 | 2019-03-07 20:06:09 |\n" +
"| 5 | 5 | 2019-03-07 20:11:27 |\n" +
"| 7 | 1 | 2019-03-07 20:13:30 |\n" +
"| 8 | 0 | 2019-03-07 20:13:41 |\n" +
"| 7 | 1 | 2019-03-07 20:17:00 |\n" +
"| 8 | 0 | 2019-03-07 20:22:20 |\n" +
"| 7 | 1 | 2019-03-07 20:23:05 |\n" +
"| 8 | 0 | 2019-03-07 20:27:35 |\n" +
"| 7 | 1 | 2019-03-07 20:27:37 |\n" +
"| 8 | 0 | 2019-03-07 20:28:01 |\n" +
"| 7 | 1 | 2019-03-07 20:37:19 |\n" +
"| 8 | 0 | 2019-03-07 20:37:27 |\n" +
"| 7 | 1 | 2019-03-07 20:37:54 |\n" +
"| 8 | 0 | 2019-03-07 20:40:11 |\n" +
"| 7 | 1 | 2019-03-07 20:44:00 |\n" +
"| 8 | 0 | 2019-03-07 20:45:00 |\n" +
"| 7 | 1 | 2019-03-07 20:47:41 |\n" +
"| 7 | 1 | 2019-03-07 20:48:43 |\n" +
"| 7 | 1 | 2019-03-07 20:48:51 |\n" +
"| 8 | 0 | 2019-03-07 20:51:11 |\n" +
"| 8 | 0 | 2019-03-07 20:54:46 |\n" +
"| 8 | 0 | 2019-03-07 20:55:36";
List<Dates> dates = new List<Dates>();
string line = "";
StringReader reader = new StringReader(input);
while ((line = reader.ReadLine()) != null)
{
string[] lineArray = line.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
Dates newDate = new Dates()
{
ID = int.Parse(lineArray[0]),
Value = int.Parse(lineArray[1]),
Time = DateTime.Parse(lineArray[2])
};
dates.Add(newDate);
}
return dates;
}
}
}
The code move the time exactly at the 15 minute interval to next interval. The smallest resolution of time is 100ns (1 tick) so subtract 1 tick I believe is the correct solution
var results = dates.GroupBy(x => new DateTime(x.Time.Year, x.Time.Month, x.Time.Day, x.Time.Hour, 15 * (x.Time.Minute / 15), 0).AddMinutes(15).AddTicks(-1))
.Select(x => x.OrderBy(y => x.Key.Subtract(y.Time)).First())
.ToList();
To group by 15 minutes from start time use this
//tick is 100ns
const long TICKS_PER_15_MINUTES = 15 * 60 * 10000000L; //minutes, seconds, ticks
DateTime minTime = dates.OrderBy(x => x.Time).First().Time;
List<Dates> results = dates.GroupBy(x => (TICKS_PER_15_MINUTES * ((x.Time.Ticks - minTime.Ticks) / TICKS_PER_15_MINUTES) + TICKS_PER_15_MINUTES - 1))
.Select(x => x.OrderBy(y => x.Key - y.Time.Ticks).First())
.ToList();
Finally if you want closest 15 minutes from min time
//tick is 100ns
const long TICKS_PER_15_MINUTES = 15 * 60 * 10000000L; //minutes, seconds, ticks
DateTime minTime = dates.OrderBy(x => x.Time).First().Time;
List<Dates> results = dates.GroupBy(x => ((x.Time.Ticks - minTime.Ticks) % TICKS_PER_15_MINUTES) < (TICKS_PER_15_MINUTES / 2)
? TICKS_PER_15_MINUTES * ((x.Time.Ticks - minTime.Ticks) / TICKS_PER_15_MINUTES)
: (TICKS_PER_15_MINUTES * ((x.Time.Ticks - minTime.Ticks) / TICKS_PER_15_MINUTES)) + TICKS_PER_15_MINUTES)
.Select(x => x.OrderBy(y => Math.Abs( x.Key - y.Time.Ticks)).First())
.ToList();