How can I group by specific timestamp intervals in

问题:

I have a list with tick objects including a pair of a double value and a timestamp.

I want to separate the list into child-lists depending on a time interval (for example 15 minutes). One list only has the tick objects from: 8:00:00 - 8:14-59 usw usw

C#-code:

var result = from tick in listTicks
group tick by tick.timestamp.Hour; 

The datetime functions like day, hour, minute work fine but a specific interval wasn't possible for me.

It is LINQ to objects. I have already got the list from the DB and I want to transform the list.

If I have data from 8-22 each day separate to 3 h he separate this way:

0-2;3-5;6-8;9-11

With code:

var result = from tick in listTicks group tick by
(tick.timestamp.Day * 10 + tick.timestamp.Hour / 3);

I want:

8-10;11-13;14-16;17-19;20-22

I want it to separate it this way.

回答1:

var result = from tick in listTicks
group tick by (tick.timestamp - DateTime.MinValue).TotalMinutes / 15 into f
from c in f
select new { StartTime = DateTime.MinValue + TimeSpan.FromMinutes(c.Key),
             Items = ... };

Fixed: DateTime.Now will cause issues :)

Bug notice: Wont work with LINQ2SQL

Fix: Left to reader :)

回答2:

Create a computed column in your SQL database, set this expression to the date and make it return the number of seconds represented by that date.

You can then group by the divided value, it should work fine. If you divide by 15 * 60, you'll get groups of 15 mins.

Edit:

var result = from tick in listTicks 
             group tick by tick.timestamp / (3 * 3600);

For the above to work, timestamp should be the number of seconds since some epoch, if you're using SQL sever this would be SqlDateTime.MinValue.