sql-server - 需要在sql server中通过四舍五入的时间或日期数字来计算

Question

我在 Ms Sql 2008 中有 2 个数据列

作为 ID 的 uniqueidentifier 列和一个日期时间列，其结果如下所示。

2011-11-06 18:02:18.030
2011-11-06 18:02:18.373
2011-11-06 18:02:57.560
2011-11-06 18:02:58.593
2011-11-06 18:03:01.717
2011-11-06 18:03:02.373
2011-11-06 18:03:03.407

除了 ID 列的复杂性之外。我只对基于 om 间隔的数据进行分组感兴趣

minutes(1,5,10,15,30)
hours (1,2)
days(1,5,10)
months(1,2)

结果应取整或以其他方式在每个间隔中仅产生 1 个唯一结果如下

2011-11-06 18:02:00 (1 Minute)
2011-11-06 18:03:00 (1 Minute)
2011-11-06 18:04:00 (1 Minute)
2011-11-06 18:05:00 (1 Minute)
2011-11-06 18:06:00 (1 Minute)

或每天

2011-11-06 00:00:00 (1 Day)
2011-11-07 00:00:00 (1 Day)
2011-11-08 00:00:00 (1 Day)
2011-11-09 00:00:00 (1 Day)
2011-11-10 00:00:00 (1 Day)

等等

欢迎任何建设性的建议。

Answer 1

您可以使用相同的技术舍入到任何日期间隔。这依赖于整数除法

SELECT
    DATEADD(minute, DATEDIFF(minute, 0, foo), 0),              -- whole minute
    DATEADD(minute, DATEDIFF(minute, 0, foo) / 5 * 5, 0),      -- 5 minute
    DATEADD(minute, DATEDIFF(minute, 0, foo) / 10 * 10, 0),    -- 10 minute
    DATEADD(minute, DATEDIFF(minute, 0, foo) / 15 * 15, 0),    -- 15 minute
    DATEADD(minute, DATEDIFF(minute, 0, foo) / 30 * 30, 0),    -- 30 minute

    DATEADD(hour, DATEDIFF(hour, 0, foo), 0),                  -- whole hour
    DATEADD(hour, DATEDIFF(hour, 0, foo) / 2 * 2, 0),          -- 2 hour

    DATEADD(day, DATEDIFF(day, 0, foo), 0),                    -- whole day
    DATEADD(day, DATEDIFF(day, 0, foo) / 5 * 5, 0),            -- 5 day
    DATEADD(day, DATEDIFF(day, 0, foo) / 10 * 10, 0),          -- 10 day

    DATEADD(month, DATEDIFF(month, 0, foo), 0),                -- whole month
    DATEADD(month, DATEDIFF(month, 0, foo) / 2 * 2, 0)         -- 2 month
FROM
    @dates;