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


I have a table with a timestamp, and I want to divide this table in the interval of hours, starting from now Going back and going back a few hours. I am unable to get the results with the T-SQL function because it passes 12 times the number of minutes between the two dates - I want the number of times to go through their minutes where it is now and now between timestamps.

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

Updates: Comments, here are some sample data, I am currently using and getting the results, as well as the results I am getting.

Sample data: 

  timestamp ********* 2010-07-20 11:00 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:40 00:00 2010-07-20 10: 16: 00.000 2010-07-20 13: 00: 00.000 2010-07-20 12:58: 00.000

Current question: 

  SELECT timestamps, DATEDIFF (HOAR, timestamp, CURRENT_TIMESTAMP) FFDF ..

Result: 

 Timestamp Diff ************************************************************************* -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:40: 00.000 2 2010-07-20 20: 16: 00.000 3 2010-07-20 13: 00: 00.000 0 2010-07-20 12:58 58: 00.000 1

What do I want to do : 

 - Now time, for example, 13:40 timestamp def *********** **** 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 40: 00.000 2 or 3 - The case of edge, I really do not care 2010-07-20 10: 16: 00.000 4 - +1 2010-07-20 13: 00: 00.000 1 - +1 2010-07-20 12:58 580000 1

I have marked results that have changed with a +1 . Besides, I really do not care whether it is 0-indexed or 1-indexed, but basically, if it is now 13:40 I want that time to get the same value 

 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 not be used, and then assuming the result is 60 and integer eg. 

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

I believe this An integer will be inserted, because the dated gives an integer, it gives the whole hour with no goals.

To use your exact query from your updated post: 

  SELECT timestamps, (DATEDIFF (minutes, timestamp, CURRENT_TIMESTAMP) / 60) AS DIFF ...

Comments

Post a Comment