Aggregate and Group by Datetime

Question

Having this data (SQL):

I call (every minute with timer )data then group into 30 minutes interval with this method:

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

And it does the job of aggregating the data, but the problem is I have to wait 30 minutes for my serie finally update if you look at the last candle its time is 17.30 when database has data for 17.47 (im UTC +2.00).

So the question is how can I group data and start draw an incomplete candle at 18.00 like all exchange platform does...

hmm finaly comparing to real echange chart i checked that result arnt that bad they got halh hour behind too time my candle update ithink im not mistaken — Zwan, Sep 02 '18 at 18:47

Gary Webb · Answer 1 · 2018-09-07T09:18:15.033

The GroupBy statement rounds down the dates, so all data from 17:30 up to 17:47 is rounded down to 17:30.

I would move the code to calculate the TimeBoundary to its own method so you can unit test it fully, using the RoundUp/RoundDown methods in this question 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);
}

Aggregate and Group by Datetime

1 Answers1