T-SQL DateDiff - “满小时前”的分区，而不是“时间分钟变为00”，因为“ [英] T-SQL DateDiff - partition by "full hours ago", rather than "times minutes turned 00 since"

问题描述

我有一个带有时间戳的表，我想把这个表分成几小时的间隔，从现在开始，往后退几个小时。我无法使用T-SQL获得所需的结果 DATEDIFF 功能，因为它计算两个日期之间分钟的次数12的次数 - 我想要他们之间的时间戳和现在之间的分钟数现在的次数 p>

在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 

我宁愿拥有：

  - 现在是为了这个例子，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或3  - ID不关心哪个
 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（或0）
 11：40-12：40 2（或1）
 10：40-11：40 3 2）
 09：40-10：40 4（或3）

解决方案

使用 DATEDIFF（分钟，.. ，然后将结果除以60并取整数值。例如

  SELECT DATEDIFF（分钟，'2010-07-20 06:00'，GETDATE（））/ 60 
  

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

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

  SELECT TimeStamp（DATEDIFF（minute，TimeStamp，CURRENT_TIMESTAMP）/ 60）AS Diff FROM ... 
  

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)

解决方案

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 ...

这篇关于T-SQL DateDiff - “满小时前”的分区，而不是“时间分钟变为00”，因为“的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！