T-SQL DateDiff - 按“完整小时前”分区，而不是“自从分钟开始转换为00的时间”

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.

我有一个带有时间戳的表,我想将这个表分成长达一小时的时间间隔,从现在开始并向后退几个小时。我无法通过T-SQL DATEDIFF函数得到我需要的结果,因为它计算了分针在两个日期之间经过12的次数 - 我想要它们分针在现在之间的分数的次数时间戳,现在。

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

在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

我用+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)

3 个解决方案

#1

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

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

 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.

我相信这将被隐式地转换为int,因为datediff返回一个int,它给出整个小时没有舍入。

To use your exact query from your updated post:

要使用您更新的帖子中的确切查询:

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

#2

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.

不幸的是,我不得不利用我对datetime数据类型的分辨率(1/300秒)的了解,因此3600000 - 3 = 3599997.如果毫秒调整是基于TimeStamp而不是GetDate()计算的,那么这不是需要,但它会更麻烦,因为派生表D中的大表达式必须在主查询中使用两次,取代了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).

计算比看起来更复杂,因为你不能只计算随机日期之间的毫秒差异,否则你会得到一个溢出错误。如果您知道可能的日期范围,您可以使用与'19000101 00:00:00.000'不同的锚定日期(上述表达式中的0)进行直接毫秒计算。

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

第二个想法,你只需要24天以上毫秒的签名长:

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

#3

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)

(您需要添加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

#1