average values for given resolution of minutes for datetime value pairs

Question

The following query seem to work quite fine:

var query =
Session.Query<SomeEvent>()
    .Where(
       p =>
       p.Timestamp >= from.Now
       && p.Timestamp <= to.Now
    )
    .GroupBy(x => x.Timestamp)
    .AsEnumerable()
    .Select(
    x =>
    new SomeEvent {Timestamp = x.Key, DisplayValue = x.Average(y => y.DisplayValue)})
    .OrderBy(x => x.Timestamp);

in averaging displayvalues if there are duplicates in terms of timestamps (datetime). I am just curious whether it would also be possible to average values based on 'resolution' (e.g. in 30 minutes)?

eeee · Accepted Answer · 2013-09-23T18:07:08.930

1

How about this? I introduced a concept called resolution level which gives you a leveled key for each range of timestamp differences: 1 if within 30 mins, 2 if within 60 mins, 3 if within 90 mins... and so on. Based on these levels, the query can average each

double resolutionInMins = 30; // In minites
double divisor = to.Now.AddMinutes(resolutionInMins).Ticks - to.Now.Ticks;

Func<DateTime, double> resolutionLevel = 
    delegate(DateTime timestamp)
{
    return Math.Ceiling((to.Now.Ticks - timestamp.Ticks) / divisor);
};

var query =
    Session.Query<SomeEvent>()
    .Where(p => 
        p.Timestamp >= from.Now && 
        p.Timestamp <= to.Now)
.GroupBy(x => new
{
    ResolutionLevel = resolutionLevel(x.Timestamp)
})
.Select(x => new
{
    ResolutionLevel = x.Key.ResolutionLevel,
    ResolutionAvgValue = x.Average(b => b.DisplayValue)
});

edited Sep 23 '13 at 18:07

answered Sep 23 '13 at 02:45

eeee

179
2
9

Thanks. Google showed me something similar but not based on datetime values. Will test your approach later. – cs0815 Sep 23 '13 at 06:18
Sorry where is Truncate defined. SomeEvent also does not have ResolutionLevel - does it have to? – cs0815 Sep 23 '13 at 07:37
1

Sorry, please ignore Truncate method.. I have fixed the code. – eeee Sep 23 '13 at 12:22
Sorry there is still the issue of the ResolutionLevel - do I have to create some dto containing ResolutionLevel for this to work. – cs0815 Sep 23 '13 at 15:04
Hi, I just edited so it does not refer to Timestamp and not instantiate SomeEvent class inside of the query... sorry for the confusion. I tried it my side and it seemed working fine. I hope I understand your requirement correctly this time – eeee Sep 23 '13 at 18:08
Would it generally be possible to map this back to SomeEvent rather than an anonymous class? – cs0815 Sep 25 '13 at 08:40

average values for given resolution of minutes for datetime value pairs

1 Answers1