T-SQL DateDiff - partition by "full hours ago", rather than "times minutes turned 00 since"

Question

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)

Answer 1

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

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

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'

Answer 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)

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