获取给定间隔和步长的日期范围 [英] Get a range of dates given an interval and step
问题描述
修改 看起来创建一个表以分钟为单位保存DateTimes以进行连接将是最有意义的. 100年的分钟记录约有5200万行.通过Ticks进行索引应该可以使查询运行得非常快.现在变成
Edit It looks like creating a table that holds the DateTimes by minutes to join against would make the most sense. 100 years worth of minutes is ~52M rows. Indexed by the Ticks should make the query run pretty fast. It now becomes
感谢大家的反馈!
我有一个名为Recurrence的类,如下所示:
I have a class called Recurrence that looks like this:
public class Recurrence
{
public int Id { get; protected set; }
public DateTime StartDate { get; protected set; }
public DateTime? EndDate { get; protected set; }
public long? RecurrenceInterval { get; protected set; }
}
这是一个实体框架POCO类.我想对此类使用标准查询运算符做两件事. (以便查询完全在服务器端运行.)
It is an entity framework POCO class. There are two things I want to do with this class all with standard query operators. (So that the query runs entirely server side).
首先,我想创建一个查询,该查询返回从开始日期到结束日期(包括给定的重复间隔)的所有日期.迭代函数很简单
First I want to create a query that returns all the dates from the start date to the end date inclusive with the given recurrence interval. The iterative function is simple
for(i=StartDate.Ticks; i<=EndDate.Ticks; i+=RecurrenceInterval)
{
yield return new DateTime(i);
}
Enumerable.Range()将是一个选项,但没有Range的长版本.我想我在这里唯一的选择是Aggregate,但是我对该功能仍然不太满意.
Enumerable.Range() would be an option but there is no long version of Range. I'm thinking my only option here is Aggregate but I'm still not very strong with that function.
最后,当我使该查询正常工作后,我想从那里返回时间范围内(即在不同的开始日期和结束日期之间)的值.使用SkipWhile/TakeWhile做到这一点很容易.
Finally once I have that query working, I want to return the values from there that are within a time window i.e. between a different start and end date. That is easy enough to do using SkipWhile/TakeWhile.
如果DateTime.Ticks为int,这就是我的处理方法
Here's how I could do it if DateTime.Ticks was an int
from recurrence in Recurrences
let range =
Enumerable
.Range(
(int)recurrence.StartDate.Ticks,
recurrence.EndDate.HasValue ? (int)recurrence.EndDate.Value.Ticks : (int)end.Ticks)
.Where(i=>i-(int)recurrence.StartDate.Ticks%(int)recurrence.RecurrenceLength.Value==0)
.SkipWhile(d => d < start.Ticks)
.TakeWhile(d => d <= end.Ticks)
from date in range
select new ScheduledEvent { Date = new DateTime(date) };
我想我需要的是可以在EF查询上执行的LongRange实现.
I guess what I need is an implementation of LongRange that could execute over an EF Query.
推荐答案
以下是产生递归点和指定子间隔的交点的函数:
Here's the function that yields the intersection of the Recurrence points and a specified subinterval:
public class Recurrence
{
public int Id { get; protected set; }
public DateTime StartDate { get; protected set; }
public DateTime? EndDate { get; protected set; }
public long? RecurrenceInterval { get; protected set; }
// returns the set of DateTimes within [subStart, subEnd] that are
// of the form StartDate + k*RecurrenceInterval, where k is an Integer
public IEnumerable<DateTime> GetBetween(DateTime subStart, DateTime subEnd)
{
long stride = RecurrenceInterval ?? 1;
if (stride < 1)
throw new ArgumentException("Need a positive recurrence stride");
long realStart, realEnd;
// figure out where we really need to start
if (StartDate >= subStart)
realStart = StartDate.Ticks;
else
{
long rem = subStart.Ticks % stride;
if (rem == 0)
realStart = subStart.Ticks;
else
// break off the incomplete stride and add a full one
realStart = subStart.Ticks - rem + stride;
}
// figure out where we really need to stop
if (EndDate <= subEnd)
// we know EndDate has a value. Null can't be "less than" something
realEnd = EndDate.Value.Ticks;
else
{
long rem = subEnd.Ticks % stride;
// break off any incomplete stride
realEnd = subEnd.Ticks - rem;
}
if (realEnd < realStart)
yield break; // the intersection is empty
// now yield all the results in the intersection of the sets
for (long t = realStart; t <= realEnd; t += stride)
yield return new DateTime(t);
}
}
这篇关于获取给定间隔和步长的日期范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!