How to get minute bin by 5 min or 10 min based on datetime variable

Transact-SQL
,
1

I need to find the minute bin & hour bin for each datetime variable similar to below table.
DATETIME , 5MinBin, 10 MinBin, Hourbin
2017-02-16 08:56:12.000 , 55 , 50 , 08
2017-02-16 16:42:12.000 , 40 , 40 , 16

However i coudn't find a way yet. Great if someone can help.

CREATE TABLE [TEST].[dbo].[dt1]
([Time] DATETIME, Location NVARCHAR(50), [Value] FLOAT)

INSERT INTO [TEST].[dbo].[dt1] VALUES
('2017-02-16 08:56:12.000','Test1',10),
('2017-02-16 08:59:12.000','Test2',20),
('2017-02-16 09:01:12.000','Test3',30),
('2017-02-16 09:11:12.000','Test4',40),
('2017-02-17 08:56:12.000','Test5',50),
('2017-02-17 08:59:12.000','Test6',60),
('2017-02-17 09:01:12.000','Test7',70),
('2017-02-17 09:11:12.000','Test8',80)

SELECT DATETIME, (DATEPART(MINUTE, [TIME]) % 5) as '5MinBin',
(DATEPART(MINUTE, [TIME]) % 10) as '10MinBin',
(DATEPART(HOUR, [TIME])) as 'HourBin'
FROM [TEST].[dbo].[dt1]
Result i'm getting
DATETIME , 5MinBin, 10 MinBin, Hourbin
2017-02-16 08:56:12.000 , 1 , 6 , 08
2017-02-16 16:42:12.000 , 2 , 5 , 16

Above Datepart() doesnot result my intention. Hope someone will help me on this,
Thanks.

2

Try this:

SELECT DATETIME, (DATEPART(MINUTE, [TIME]) / 5 * 5) as '5MinBin', 
             (DATEPART(MINUTE, [TIME]) / 10 * 10) as '10MinBin',
            (DATEPART(HOUR, [TIME])) as 'HourBin' 
FROM [TEST].[dbo].[dt1]
2 Likes
3

Thanks

4

Thanks for the help.

How can i update the same table by adding two columns for [5MinBin] , [10MinBin], [HourBin]?

5

As computed columns:

1 Like
6

Thank you very much.

I also wants a new column to place datetime to the nearest 10 minute. I'm getting the nearest 10th munute using below select code.
Select [time],
DateAdd(minute, 10 * ((60 * Datepart(hour, [TIME]) + Datepart(Minute, [TIME])+
Case When DatePart(second, [TIME]) < 30
Then 4 Else 6 End) / 10),
DateAdd(day, DateDiff(day, 0, [TIME]), 0))
from [TEST].[dbo].[dt1];

How can i add a column into my table using such a select command result.

7

Can you verify if the formula is correct? I ran a sample below and it doesn't seem to round the time correctly:

with n(n) as (select 0 union all select n+1 from n where n<3600)
, dt1(time) as (select DATEADD(s,n,'2017-04-28 00:00:00') from n)
Select [time],
DateAdd(minute, 10 * ((60 * Datepart(hour, [TIME]) + Datepart(Minute, [TIME])+
Case When DatePart(second, [TIME]) < 30
Then 4 Else 6 End) / 10),
DateAdd(day, DateDiff(day, 0, [TIME]), 0))
from [dt1]
order by time
option (maxrecursion 3600)

Look at 04:30 - 04:59, then 5:00 - 05:30. Similar transitions occur in the other 5 minute intervals throughout the entire hour.

8

Opps sorry, i coluld have reviewed my code with the result.

I think i rectified it here. Hope there is a better way than this as it's so lenghty.

Select [time],
DateAdd(minute, ((60 * Datepart(hour, [TIME]) + case when ((Datepart(Minute, [TIME])+ Case When DatePart(second, [TIME]) < 30 Then 0 Else 1 End) % 5)>2.5 THEN
((Datepart(Minute, [TIME])+ Case When DatePart(second, [TIME]) < 30 Then 0 Else 1 End)/55)+5 ELSE
((Datepart(Minute, [TIME])+ Case When DatePart(second, [TIME]) < 30 Then 0 Else 1 End)/55) end)),
DateAdd(day, DateDiff(day, 0, [TIME]), 0)) 'Nearest5Minute',
DateAdd(minute, ((60 * Datepart(hour, [TIME]) + case when ((Datepart(Minute, [TIME])+ Case When DatePart(second, [TIME]) < 30 Then 0 Else 1 End) % 10)>5 THEN
((Datepart(Minute, [TIME])+ Case When DatePart(second, [TIME]) < 30 Then 0 Else 1 End)/1010)+10 ELSE
((Datepart(Minute, [TIME])+ Case When DatePart(second, [TIME]) < 30 Then 0 Else 1 End)/1010) end)),
DateAdd(day, DateDiff(day, 0, [TIME]), 0)) 'Nearest10Minute'
from [TEST].[dbo].[dt1];

I hope the above works.

I glad to know how to insert this select statement result into the same table as new columns.

Thank you very much.

9

I've been testing alternative calculations but they require a common table expression (CTE) which would not be supported as a computed column in the table definition. Would a CTE meet your needs?