T-SQL DateDiff - 按“整小时前”分区，而不是“自 00 点起的时间分钟数”分区

发布于 2024-09-10 18:19:10 字数 1786 浏览 3 评论 0原文

我有一个带有时间戳的表，我想将该表划分为一小时长的间隔，从现在开始向后几个小时。我无法使用 T-SQL DATEDIFF 函数，因为它计算分针在两个日期之间经过 12 的次数 - 我想要分针在时间戳和现在之间经过现在位置的次数。

在 T-SQL 中是否有直接的方法来做到这一点？

更新： 为了回应评论，这里有一些示例数据、我当前使用的查询和我得到的结果，以及我想要的结果。

示例数据：

TimeStamp
*********
2010-07-20 11:00:00.000
2010-07-20 10:44:00.000
2010-07-20 10:14:00.000
2010-07-20 11:00:00.000
2010-07-20 11:40:00.000
2010-07-20 10:16:00.000
2010-07-20 13:00:00.000
2010-07-20 12:58:00.000

当前查询：

SELECT TimeStamp, DATEDIFF(HOUR, TimeStamp, CURRENT_TIMESTAMP) AS Diff FROM ...

结果：

    TimeStamp                   Diff
    *********                   ****
    2010-07-20 11:00:00.000     2
    2010-07-20 10:44:00.000     3
    2010-07-20 10:14:00.000     3
    2010-07-20 11:00:00.000     2
    2010-07-20 11:40:00.000     2
    2010-07-20 10:16:00.000     3
    2010-07-20 13:00:00.000     0
    2010-07-20 12:58:00.000     1

我想要的：

    -- The time is now, for the sake of the example, 13:40

    TimeStamp                   Diff
    *********                   ****
    2010-07-20 11:00:00.000     3 -- +1
    2010-07-20 10:44:00.000     3
    2010-07-20 10:14:00.000     4 -- +1
    2010-07-20 11:00:00.000     3 -- +1
    2010-07-20 11:40:00.000     2 or 3 -- edge case, I don't really care which
    2010-07-20 10:16:00.000     4 -- +1
    2010-07-20 13:00:00.000     1 -- +1
    2010-07-20 12:58:00.000     1

我已用 +1 标记了更改的结果。另外，我并不关心这是 0 索引还是 1 索引，但基本上，如果现在是 13:40，我希望获得相同值的时间跨度为

    12:40-13:40    1 (or 0)
    11:40-12:40    2 (or 1)
    10:40-11:40    3 (or 2)
    09:40-10:40    4 (or 3)

原文

I have a table with timestamps, and I want to partition this table into hour-long intervals, starting at now and going backwards a couple of hours. I'm unable to get the results I need with the T-SQL DATEDIFF function, since it counts the number of times the minute hand passes 12 between the two dates - I want the number of times them minute hand passes where it is now between the timestamp and now.

Is there a straightforward way to do this in T-SQL?

Update:
In response to comments, here's some sample data, the query I'm currently using and the results I'm getting, as well as the results I want.

Sample data:

TimeStamp
*********
2010-07-20 11:00:00.000
2010-07-20 10:44:00.000
2010-07-20 10:14:00.000
2010-07-20 11:00:00.000
2010-07-20 11:40:00.000
2010-07-20 10:16:00.000
2010-07-20 13:00:00.000
2010-07-20 12:58:00.000

Current query:

SELECT TimeStamp, DATEDIFF(HOUR, TimeStamp, CURRENT_TIMESTAMP) AS Diff FROM ...

Results:

    TimeStamp                   Diff
    *********                   ****
    2010-07-20 11:00:00.000     2
    2010-07-20 10:44:00.000     3
    2010-07-20 10:14:00.000     3
    2010-07-20 11:00:00.000     2
    2010-07-20 11:40:00.000     2
    2010-07-20 10:16:00.000     3
    2010-07-20 13:00:00.000     0
    2010-07-20 12:58:00.000     1

What I'd rather have:

    -- The time is now, for the sake of the example, 13:40

    TimeStamp                   Diff
    *********                   ****
    2010-07-20 11:00:00.000     3 -- +1
    2010-07-20 10:44:00.000     3
    2010-07-20 10:14:00.000     4 -- +1
    2010-07-20 11:00:00.000     3 -- +1
    2010-07-20 11:40:00.000     2 or 3 -- edge case, I don't really care which
    2010-07-20 10:16:00.000     4 -- +1
    2010-07-20 13:00:00.000     1 -- +1
    2010-07-20 12:58:00.000     1

I've marked the results that changed with a +1. Also, I don't really care if this is 0-indexed or 1-indexed, but basically, if it's now 13:40 I want the time spans that get the same value to be

    12:40-13:40    1 (or 0)
    11:40-12:40    2 (or 1)
    10:40-11:40    3 (or 2)
    09:40-10:40    4 (or 3)

分享到QQ

分享到微博

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

发布评论

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

听不够的曲调 2024-09-17 18:19:10

你能不能只使用 DATEDIFF(min,.. ，然后将结果除以 60 并获取整数值。例如，

 SELECT DATEDIFF(minute, '2010-07-20 06:00', GETDATE())/60

我相信这将隐式转换为 int，因为 datediff 返回一个 int，它给出：

要使用更新帖子中的确切查询，请执行以下操作

SELECT TimeStamp, (DATEDIFF(minute, TimeStamp, CURRENT_TIMESTAMP) /60) AS Diff FROM ...

Can you not just use DATEDIFF(minute,.. and then divide the result by 60 and take the integer value. e.g.

 SELECT DATEDIFF(minute, '2010-07-20 06:00', GETDATE())/60

I believe this will be implicitly cast as an int as datediff returns an int, it gives whole hours with no rounding.

To use your exact query from your updated post:

SELECT TimeStamp, (DATEDIFF(minute, TimeStamp, CURRENT_TIMESTAMP) /60) AS Diff FROM ...

回复收藏 0 原文

柳絮泡泡 2024-09-17 18:19:10

您可以对此进行分组：

SELECT DateDiff(Hour, 0, GetDate() - TimeStamp)

如果您想知道它代表的时间，请计算回来：

DateAdd(Hour, -DateDiff(Hour, 0, GetDate() - TimeStamp), GetDate())

如果您不喜欢减去日期，那么它仍然可以完成，但会变得有点困难。我没有在黑暗中拍摄，而是提出了一个查询来证明这是正确的。

SELECT
   TimeStamp,
   Now = GetDate(),
   HourDiff = DateDiff(Hour, 0, GetDate() - TimeStamp),
   HourCalc = DateAdd(Hour, -DateDiff(Hour, 0, GetDate() - TimeStamp), GetDate()),
   HourDiff2 = DateDiff(Hour, DateAdd(Millisecond, AdjustMs, TimeStamp), DateAdd(Millisecond, AdjustMs, GetDate())),
   HourCalc2 = DateAdd(Hour, -DateDiff(Hour, DateAdd(Millisecond, AdjustMs, TimeStamp), DateAdd(Millisecond, AdjustMs, GetDate())), GetDate())
FROM
   (
      SELECT DateAdd(Second, -3559, GetDate())
      UNION ALL SELECT DateAdd(Second, -3600, GetDate())
      UNION ALL SELECT DateAdd(Second, -3601, GetDate())
   ) x (TimeStamp)
   CROSS JOIN (
      SELECT 3599997 - DateDiff(Millisecond, 0, DateAdd(Hour, -DateDiff(Hour, 0, GetDate()), GetDate()))
   ) D (AdjustMs)

不幸的是，我不得不利用我对日期时间数据类型分辨率（1/300 秒）的了解，因此 3600000 - 3 = 3599997。如果毫秒调整是基于 TimeStamp 而不是 GetDate() 计算的，那么这不会是需要，但它会更混乱，因为派生表 D 中的大表达式必须在主查询中使用两次，以替换 AdjustMs。

计算比看起来必要的更复杂，因为您不能只计算随机日期之间的毫秒差异，否则您会收到溢出错误。如果您知道可能的日期范围，您可能可以使用与“19000101 00:00:00.000”（上述表达式中的 0）不同的锚日期进行直接毫秒计算。

再想一想，您只能在有符号长整型中获得 24 天以上的毫秒数：

SELECT DateAdd(Millisecond, 2147483647, 0) = '1900-01-25 20:31:23.647'

You can group on this:

SELECT DateDiff(Hour, 0, GetDate() - TimeStamp)

If you want to know the time this represents, calc it back:

DateAdd(Hour, -DateDiff(Hour, 0, GetDate() - TimeStamp), GetDate())

If you don't like subtracting dates, then it can still be done but becomes a bit harder. Instead of just shoot in the dark I worked up a query to prove this is correct.

SELECT
   TimeStamp,
   Now = GetDate(),
   HourDiff = DateDiff(Hour, 0, GetDate() - TimeStamp),
   HourCalc = DateAdd(Hour, -DateDiff(Hour, 0, GetDate() - TimeStamp), GetDate()),
   HourDiff2 = DateDiff(Hour, DateAdd(Millisecond, AdjustMs, TimeStamp), DateAdd(Millisecond, AdjustMs, GetDate())),
   HourCalc2 = DateAdd(Hour, -DateDiff(Hour, DateAdd(Millisecond, AdjustMs, TimeStamp), DateAdd(Millisecond, AdjustMs, GetDate())), GetDate())
FROM
   (
      SELECT DateAdd(Second, -3559, GetDate())
      UNION ALL SELECT DateAdd(Second, -3600, GetDate())
      UNION ALL SELECT DateAdd(Second, -3601, GetDate())
   ) x (TimeStamp)
   CROSS JOIN (
      SELECT 3599997 - DateDiff(Millisecond, 0, DateAdd(Hour, -DateDiff(Hour, 0, GetDate()), GetDate()))
   ) D (AdjustMs)

Unfortunately, I had to exploit my knowledge of the datetime datatype's resolution (1/300th of a second), thus 3600000 - 3 = 3599997. If the millisecond adjustment was calculated based on the TimeStamp instead of GetDate() then this wouldn't be needed, but it would be a lot messier since the big expression inside derived table D would have to be used twice in the main query, replacing AdjustMs.

The calculations are more complicated than might seem necessary because you can't just calculate milliseconds difference between random dates or you'll get an overflow error. If you know the date ranges possible you might be able to get away with doing direct millisecond calculations using a different anchor date than '19000101 00:00:00.000' (the 0 in the above expressions).

On second thought, you only get 24+ days of milliseconds into a signed long:

SELECT DateAdd(Millisecond, 2147483647, 0) = '1900-01-25 20:31:23.647'

回复收藏 0 原文

佼人 2024-09-17 18:19:10

我会使用

FLOOR(24 * CAST(CURRENT_TIMESTAMP-[TimeStamp] as float))

测试用例

DECLARE @GetDate datetime
set @GetDate = '2010-07-20 13:40:00.000';

WITH TestData As
(
select CAST('2010-07-20 11:00:00.000' AS DATETIME) AS [TimeStamp]  UNION ALL
select '2010-07-20 10:44:00.000'  UNION ALL    
select '2010-07-20 10:14:00.000'  UNION ALL   
select '2010-07-20 11:00:00.000'  UNION ALL   
select '2010-07-20 11:40:00.000'  UNION ALL   
select '2010-07-20 10:16:00.000'  UNION ALL   
select '2010-07-20 13:00:00.000'  UNION ALL  
select '2010-07-20 12:58:00.000'
)

SELECT [TimeStamp], FLOOR(24 * CAST(@GetDate-[TimeStamp] as float))  AS Diff
FROM TestData

结果

（您需要添加1才能获得您发布的确切结果，但您说您不介意0或1索引）

TimeStamp               Diff
----------------------- ----------------------
2010-07-20 11:00:00.000 2
2010-07-20 10:44:00.000 2
2010-07-20 10:14:00.000 3
2010-07-20 11:00:00.000 2
2010-07-20 11:40:00.000 2
2010-07-20 10:16:00.000 3
2010-07-20 13:00:00.000 0
2010-07-20 12:58:00.000 0

I'd use

FLOOR(24 * CAST(CURRENT_TIMESTAMP-[TimeStamp] as float))

Test Case

DECLARE @GetDate datetime
set @GetDate = '2010-07-20 13:40:00.000';

WITH TestData As
(
select CAST('2010-07-20 11:00:00.000' AS DATETIME) AS [TimeStamp]  UNION ALL
select '2010-07-20 10:44:00.000'  UNION ALL    
select '2010-07-20 10:14:00.000'  UNION ALL   
select '2010-07-20 11:00:00.000'  UNION ALL   
select '2010-07-20 11:40:00.000'  UNION ALL   
select '2010-07-20 10:16:00.000'  UNION ALL   
select '2010-07-20 13:00:00.000'  UNION ALL  
select '2010-07-20 12:58:00.000'
)

SELECT [TimeStamp], FLOOR(24 * CAST(@GetDate-[TimeStamp] as float))  AS Diff
FROM TestData

Results

(You would need to add 1 to get the exact results you posted but you say you aren't bothered about 0 or 1 indexed)

TimeStamp               Diff
----------------------- ----------------------
2010-07-20 11:00:00.000 2
2010-07-20 10:44:00.000 2
2010-07-20 10:14:00.000 3
2010-07-20 11:00:00.000 2
2010-07-20 11:40:00.000 2
2010-07-20 10:16:00.000 3
2010-07-20 13:00:00.000 0
2010-07-20 12:58:00.000 0

回复收藏 0 原文

~没有更多了~