每周时间安排范围在几天之间

发布于 2024-11-10 17:49:58 字数 401 浏览 7 评论 0原文

我制定了从周五晚上 2011-05-27 18:00 到周一早上的每周日程 2011-05-30 06:00。此时间表从2011-05-27开始每周执行。我们如何确定事件时间（2011-06-06 19:00）落在这个每周时间表之间？

更新

ID    StartTime         EndTime            Recurrence  TimeDifferenceInSeconds
1     2011-05-27 18:00  2011-05-30 06:00   1           216000

这里的复发是每周一次。

问候，

格里普蒂

原文

I have a weekly schedule set from Friday evening 2011-05-27 18:00 to Monday morning
2011-05-30 06:00. This schedule executed on every week starts from 2011-05-27. How we can identify an event time (2011-06-06 19:00) falls between this weekly schedule ?

UPDATE

ID    StartTime         EndTime            Recurrence  TimeDifferenceInSeconds
1     2011-05-27 18:00  2011-05-30 06:00   1           216000

Here recurrence is every one week.

Regards,

Greepty

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

瑶笙 2024-11-17 17:49:59

解决此问题的一种可能方法：

获取给定日期/时间与 StartTime 之间的整周数。
将周数添加到StartTime以获取最晚的开始时间。
计算最晚开始时间与给定日期/时间之间的差值。
检查该差异是否小于 TimeDifferenceInSeconds。

这就是它的实现方式：

SELECT *
FROM Schedules
WHERE StartTime < @Date AND TimeDifferenceInSeconds >
  DATEDIFF(ss, DATEADD(week, DATEDIFF(day, StartTime, @Date) / 7, StartTime), @Date)

这将返回 @Date 所属的所有时间表。

更适合读者的版本：

WITH NewSchedules AS (
  SELECT
    *,
    NewStartTime = DATEADD(week, DATEDIFF(day, StartTime, @Date) / 7, StartTime)
  FROM Schedule
  WHERE StartTime < @Date
)
SELECT *
FROM NewSchedules
WHERE TimeDifferenceInSeconds > DATEDIFF(ss, NewStartTime , @Date)

A possible approach to this problem:

Get the number of whole weeks between the given date/time and StartTime.
Add the number of weeks to StartTime to obtain the latest start time.
Calculate the difference between the latest start time and the given date/time.
Check if that difference is less than TimeDifferenceInSeconds.

And this is how it could be implemented:

SELECT *
FROM Schedules
WHERE StartTime < @Date AND TimeDifferenceInSeconds >
  DATEDIFF(ss, DATEADD(week, DATEDIFF(day, StartTime, @Date) / 7, StartTime), @Date)

This returns all the schedules that @Date falls on.

A more reader-friendly version:

WITH NewSchedules AS (
  SELECT
    *,
    NewStartTime = DATEADD(week, DATEDIFF(day, StartTime, @Date) / 7, StartTime)
  FROM Schedule
  WHERE StartTime < @Date
)
SELECT *
FROM NewSchedules
WHERE TimeDifferenceInSeconds > DATEDIFF(ss, NewStartTime , @Date)

回复收藏 0 原文