按Datetime聚合和分组

Question

具有此数据(SQL)：

​

​

我调用(使用计时器的每一分钟)数据，然后使用此方法将数据分组为30分钟间隔：

public void GetData()
{
    try
    {
        using (crypto_dbEntities1 context = new crypto_dbEntities1(con))
        {
            var result = context.kraken_btc.Where(x => x.date >= LastRecordedPoint).ToList();
            result = AggregateCandlesIntoRequestedTimePeriod(Period.Minute, Period.HalfAnHour, result);

            foreach (var data in result.OrderBy(x => x.date))
            {
                data.date = DateTime.SpecifyKind(data.date.DateTime, DateTimeKind.Utc).ToUniversalTime();

                Point pb = new Point
                       {
                            Date = data.date.DateTime.ToLocalTime(),
                            JDate = (long)data.javaDate,
                            Open = (double)data.open,
                            High = (double)data.hight,
                            Close = (double)data.close,
                            Low = (double)data.low,
                            Volume = (long)data.volume,
                       };

                if (pb.Date <= LastRecordedPoint)
                {
                   MainCollection.Last().Date = data.date.DateTime.ToLocalTime();
                   MainCollection.Last().Close = (double)data.close;
                   MainCollection.Last().High = (double)data.hight;
                   MainCollection.Last().Open = (double)data.open;
                   MainCollection.Last().Low = (double)data.low;
                   MainCollection.Last().Volume = (long)data.volume;

                   Debug.WriteLine(pb.Date + " Updated data ..");
               }
               else
               {
                   MainCollection.Add(pb);
               }

               LastRecordedPoint = pb.Date;
            }
        }
    }
    catch (Exception err)
    {
        MessageBox.Show(err.ToString());
    }
}

public enum Period
{
    Minute = 1,
    FiveMinutes = 5,
    TenMinutes = 10,
    QuarterOfAnHour = 15,
    HalfAnHour = 30,
    Hour = 60
}

private List<kraken_btc> AggregateCandlesIntoRequestedTimePeriod(Period rawPeriod, Period requestedPeriod, List<kraken_btc> candles)
{
    int rawPeriodDivisor = (int)requestedPeriod;
    candles = candles.GroupBy(g => new { TimeBoundary = new DateTime(g.date.Year, g.date.Month, g.date.Day, g.date.Hour, (g.date.Minute / rawPeriodDivisor) * rawPeriodDivisor, 0) })
                     .Select(s => new kraken_btc
                        {
                            date = s.Key.TimeBoundary,
                            hight = s.Max(z => z.hight),
                            low = s.Min(z => z.low),
                            open = s.First().open,
                            close = s.Last().close,
                            volume = s.Sum(z => z.volume),
                        })
                     .OrderBy(o => o.date)
                     .ToList();

    return candles;
}

它执行聚合数据工作，但问题是我必须等待30分钟，以便我的系列最终更新。如果你看最后一根蜡烛，当数据库有17.47 (im UTC +2.00)的数据时，它的时间是17.30。

所以问题是，我如何将数据分组，并像所有exchange平台一样，在18.00开始画一个不完整的蜡烛……

​

​

Answer 1

GroupBy语句向下舍入日期，因此从17:30到17:47的所有数据都向下舍入为17:30。

我会将计算TimeBoundary的代码移到它自己的方法中，这样您就可以使用这个问题How can I round up the time to the nearest X minutes? by redent84中的四舍五入/四舍五入方法对它进行完整的单元测试。

public static DateTime RoundUp(this DateTime dt, TimeSpan d)
{
    var modTicks = dt.Ticks % d.Ticks;
    var delta = modTicks != 0 ? d.Ticks - modTicks : 0;
    return new DateTime(dt.Ticks + delta, dt.Kind);
}

public static DateTime RoundDown(this DateTime dt, TimeSpan d)
{
    var delta = dt.Ticks % d.Ticks;
    return new DateTime(dt.Ticks - delta, dt.Kind);
}