从数据库特定的时间间隔获取值 [英] Get values from database with specific time interval

问题描述

我想从数据库在C＃中特定的时间间隔获取价值观和需要一个单一的查询。 这是我的数据库看起来像

I want to fetch values from database with specific intervals in C# and need a single query for that. This is how my Database looks like

Id   SensorId    Value        CreatedOn   
------------------------------------------------      
1       8        33.5      15-11-2012  5:48 PM        
2       5        49.2      15-11-2012  5:48 PM
3       8        33.2      15-11-2012  5:49 PM  
4       5        48.5      15-11-2012  5:49 PM  
5       8        31.8      15-11-2012  5:50 PM 
6       5        42.5      15-11-2012  5:50 PM 
7       8        36.5      15-11-2012  5:51 PM 
8       5        46.5      15-11-2012  5:51 PM 
9       8        39.2      15-11-2012  5:52 PM 
10      5        44.4      15-11-2012  5:52 PM 
11      8        36.5      15-11-2012  5:53 PM 
12      5        46.5      15-11-2012  5:53 PM 
13      8        39.2      15-11-2012  5:54 PM 
14      5        44.4      15-11-2012  5:54 PM 
..      ..       .....     ...................

的时间间隔是在分钟。 所以，如果时间间隔为10分钟，那么我们所需要的值在5:48，5:58，6:08等等......

The interval is in minutes. So, if the interval is 10 minutes, then we need the values at 5:48, 5:58, 6:08 and so on...

我试着用whil​​e循环这样做，但它采取了大量的时间，因为我拍摄多数据库查询。

I tried doing it with a while loop but it is taking a lot of time as i shoot multiple queries to the database.

有没有办法获得的数据在一个单一的查询？

Is there any way of getting the data in a single query?

推荐答案

您可以使用的日期部分 > <$ C $随着模量，以获得匹配的行（例如，@interval = 10，@Offset = 8）：

You can use datepart along with a modulus to get the matching rows (eg, @interval = 10, @offset = 8):

SELECT * FROM table
WHERE datepart(minute, CreatedOn) % @interval = @offset

---

修改

---

Edit

请注意，上述的是不是一个的一般的选择由间隔溶液。它将工作跨越的小时的（并且因此跨天）为间隔如2，3，4，5 ...任何分钟间隔以及分成60.

Note that the above isn't a general solution of selecting by intervals. It will work across hours (and therefore across days) for intervals like 2, 3, 4, 5 ... any minute interval which divides into 60.

如果你想使用一个陌生的间隔像7分钟，那么你就必须定义一个起始时间间隔和计算的总分钟的每一行，包容小时/天。在这一点上你最好创建一个索引，在计算表列，根据用户定义的函数，计算的时间间隔问题。

If you want to use a strange interval like 7 minutes, then you'd have to define a starting time for the interval and calculate the total minutes for each row, inclusive of hours/days. At that point you'd be best to create an indexed, computed column on the table, based on a user-defined function that calculates the interval in question.

这篇关于从数据库特定的时间间隔获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！