CODEWITHSUNDEEP
t-sql
ddrjca
ddrjca

Reputation: 482

Grouping by 90 Minute Intervals

I'm trying to group records with a DATETIME field into 90 minute intervals.

I can group by any number of minutes which 60 is evenly divisible by using:

SET @Interval = 3 -- Every 3 Minutes
SELECT   CAST(
     CONVERT(VARCHAR(8), [TIME_STAMP],112)            + ' ' +
     CONVERT(VARCHAR(2), DATEPART(hh, [TIME_STAMP]))  + ':' +
     CONVERT(VARCHAR(2), DATEPART(mi, [TIME_STAMP]) / @Interval * @Interval)
             AS DATETIME) INTERVAL_TIME

or group by whole hours using:

SET @Interval = 4 --Every 4 hours 
SELECT CAST(
    CONVERT(VARCHAR(8), [TIME_STAMP],112) + ' ' +
    CONVERT(VARCHAR(2), DATEPART(hh, [TIME_STAMP]) / @Interval * @Interval ) 
            + ':00'
            AS DATETIME) INTERVAL_TIME

How do I group by 90 minutes or 1.5 hours ?

Upvotes: 1

Views: 340

Answers (3)

flo
flo

Reputation: 10281

You can use DATEDIFF(minute, 0, [TimeStamp]) to get the amount of minutes gone from epoch. Then you can simply divide by 90 and get the group you are in.

Maybe you have to add some offset. With DATEADD(minute, [your group]*90, 0) you get the starting time of that special group.

For your code (+ reduction of the many casts):

DECLARE @Interval int = 90
DECLARE @OffsetTime datetime = 0

SELECT DATEADD(minute, (DATEDIFF(minute, @OffsetTime, [TimeStamp]) / @Interval) * @Interval, @OffsetTime) INTERVAL_TIME

Notice that this code works for any interval you want to group by.

Upvotes: 2

th1rdey3
th1rdey3

Reputation: 4378

assuming you have a table with transaction_date column which is of datetime, you could do something like this -

select count(1) from tbl_master group by datediff(minute,cast(floor(cast(transaction_date as float)) as datetime),transaction_date)/90

here we are casting the transaction_date to float and then flooring it. therefore the time part will be removed from the datetime. then we are taking the total minutes passed using datediff and dividing it by 90 for 1.5 hours.

Upvotes: 0

CathalMF
CathalMF

Reputation: 10055

Well there are 16 individual 90minute intervals in a day and each day is made up of 1440 minutes.

Calculate the current minute of the day and calculate its interval. Assume its 15:20

ROUND(1440 / ((DATEPART(hh, [TIME_STAMP]) * 60) + DATEPART(mm, [TIME_STAMP])), 0, 1)

This turns into:

ROUND(1440 / ((15*60) + 20), 0, 1)

So you're answer is 10. The 10th interval of the day. Add this calculation to your columns and group by it.

SELECT Col1, Col2, ROUND(1440 / ((DATEPART(hh, [TIME_STAMP]) * 60) + DATEPART(mm, [TIME_STAMP])), 0, 1)
    FROM MyTable
    GROUP BY    ROUND(1440 / ((DATEPART(hh, [TIME_STAMP]) * 60) + DATEPART(mm, [TIME_STAMP])), 0, 1)

Upvotes: 1

Related Questions