Geek Answers Handbook

T-SQL DateDiff - section "full hour ago", but not "time, minute from 00",

I have a table with timestamps, and I want to split this table into hourly intervals, starting from this moment and stepping back a couple of hours. I can’t get the results I need with the DATEDIFF function , because it counts the number of times the minute hand goes 12 between two dates - I want the number of times that the minutes rang out, where it is now between the timestamp and now.

Is there an easy way to do this in T-SQL?

Update: In response to the comments, here are some examples of the data, the query I'm currently using, and the results I get, 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 request:

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 would prefer:

    - 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)
+5
3

DATEDIFF(minute,.., 60 . .

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

, int, dateiff int, .

:

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

:

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)

, datetime datetime (1/300th ), , 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'
0

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
0

More articles:


All Articles