Clever Geek Handbook

SQL Bucket timestamp in 2 hour increments

I want to write sql in order to expose time in 2 hour increments. For example, 0-2, 2-4, 6-8, ......... 18-20, 20-22, 22-24

Time             I want it to be
6/8/2015 20:49       20-22
6/5/2015 12:47       12-14
6/9/2015 16:46       16-18

Thank,

+3


source to share


3 answers


You can use a case expression and some simple arithmetic to group the times into buckets:

select
    time, 
    case 
       when datepart(hour, time) % 2 = 0 then  -- n % 2 = 0 determines if hour is even
          cast(datepart(hour, time) as varchar(2))
          + '-'
          + cast(datepart(hour, time) + 2 as varchar(2))
       else  -- hour is odd
          cast(datepart(hour, time) - 1 as varchar(2))
          + '-'
          + cast(datepart(hour, time) + 1 as varchar(2))
    end as bucket
from t

Note that I made the assumption that odd hours should be placed in even buckets and that there should not be any odd buckets (e.g. 1-3, 3-5, etc.).



Sample SQL script

Output example:

|                   time | bucket |
|------------------------|--------|
| June, 08 2015 00:49:00 |    0-2 |
| June, 08 2015 23:49:00 |  22-24 |
| June, 08 2015 20:49:00 |  20-22 |
| June, 05 2015 12:47:00 |  12-14 |
| June, 05 2015 13:47:00 |  12-14 |
| June, 09 2015 16:46:00 |  16-18 |
+1


source


Try this: SELECT (RTRIM (CAST (DATEPART (HOUR, GETDATE ()) AS CHAR)) + '-' + CAST ((DATEPART (HOUR, GETDATE ()) + 2) AS CHAR))



0


source


Simple integer division can drop all the right buckets you need. For example, 1/2 = 0, 2/2 = 1, 3/2 = 1, etc. After that, it's just a matter of formatting the output:

select
    time, 
    cast((datepart(hour, time)/2)*2 as varchar(2))+'-'+ cast((datepart(hour, time)/2)*2+2 as varchar(2)) as bucket
from t

Note that the default division of two integers is integer division, so this works.

0


source






More articles:


All Articles