Rounding to the nearest 15 seconds

Question

Rounding to the nearest 15 seconds

alex.palmer

SSCrazy

Points: 2292
More actions
November 13, 2017 at 4:47 am

#329513

Hi
I've imported perfmon data from 100 SQL servers into a central database to analyse the results.
The time field has imported as a Char 24 and is in the format 2017-11-10 09:47:43.935.
I'd like to round this data to the nearest 15 seconds, (the interval of the perfmon collection) so I can group by the time.
Any suggestions?
Alex

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

alex.palmer - Monday, November 13, 2017 4:47 AM
Hi
I've imported perfmon data from 100 SQL servers into a central database to analyse the results.
The time field has imported as a Char 24 and is in the format 2017-11-10 09:47:43.935.
I'd like to round this data to the nearest 15 seconds, (the interval of the perfmon collection) so I can group by the time.
Any suggestions?
Alex

Quick suggestion
😎

declare @dt datetime = '2017-11-10 09:47:43.935'

select dateadd(second, ((datepart(second,convert(datetime2(0),@dt,0)) / 15) * 15),dateadd(second,-datepart(second,convert(datetime2(0),@dt,0)),convert(datetime2(0),@dt,0)))

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Or this - it has fewer moving parts and seems to run a little bit faster than Eirikur's.

SELECT DATEADD(second,-DATEPART(second,create_date)%15,DATEADD(ms,-DATEPART(ms,create_date),create_date)) FROM sys.all_objects

John

alex.palmer SSCrazy Points: 2292 More actions · Answer 3

Hi

Thanks for the answers I used Eirikur Eiriksson answer in the end because I found the code easier.

Looks like I'm not the first personm to have issues with the date field for perfmon. I had to use this

convert(datetime,substring(CounterDateTime,1,23)
From here https://www.sqlservercentral.com/Forums/953187/Problem-to-convert-a-char24-field-to-datetime
to get a date\time I could use.

Cheers

Alex

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 4

If both simplicity and performance are important AND the datetimes will always be > '2000-01-01', then the following will do the trick. (Replace the sample date with the column name).

SELECT DATEADD(ss,DATEDIFF(ss,36524,'2017-11-10 09:47:43.935')/15*15,36524) -- 36524 = '2000-01-01';

Because of the limit of positive integers being 2,147,483,647 for seconds, the following code demonstrates when this method will quit working...

SELECT DATEADD(ss,2147483647,36524);

... so it's good to go for at least the next 50 years without modification. 😉

As a bit of a sidebar, this may work for 68 years prior to the year 2000 but I've not tested for that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100946 More actions · Answer 5

Jeff Moden - Monday, November 13, 2017 8:15 AM
If both simplicity and performance are important AND the datetimes will always be > '2000-01-01', then the following will do the trick. (Replace the sample date with the column name).
SELECT DATEADD(ss,DATEDIFF(ss,36524,'2017-11-10 09:47:43.935')/15*15,36524) -- 36524 = '2000-01-01';
Because of the limit of positive integers being 2,147,483,647 for seconds, the following code demonstrates when this method will quit working...
SELECT DATEADD(ss,2147483647,36524);
... so it's good to go for at least the next 50 years without modification. 😉
As a bit of a sidebar, this may work for 68 years prior to the year 2000 but I've not tested for that.

I don't see any need for the obscure 36524, just use the base date directly:

SELECT DATEADD(SECOND, DATEDIFF(SECOND, '20000101', '2017-11-10 09:47:43.935')/15*15, '20000101')

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 6

ScottPletcher - Monday, November 13, 2017 11:46 AM
Jeff Moden - Monday, November 13, 2017 8:15 AM
If both simplicity and performance are important AND the datetimes will always be > '2000-01-01', then the following will do the trick. (Replace the sample date with the column name).
SELECT DATEADD(ss,DATEDIFF(ss,36524,'2017-11-10 09:47:43.935')/15*15,36524) -- 36524 = '2000-01-01';
Because of the limit of positive integers being 2,147,483,647 for seconds, the following code demonstrates when this method will quit working...
SELECT DATEADD(ss,2147483647,36524);
... so it's good to go for at least the next 50 years without modification. 😉
As a bit of a sidebar, this may work for 68 years prior to the year 2000 but I've not tested for that.
I don't see any need for the obscure 36524, just use the base date directly:
SELECT DATEADD(SECOND, DATEDIFF(SECOND, '20000101', '2017-11-10 09:47:43.935')/15*15, '20000101')

INTs tend to be a tiny bit faster than strings. The comment removes any doubt as to what the number is. There's also no need to spell out "SECOND". If you don't know what "SS" stands for, then you may have bigger problems and I would be concerned if someone that didn't know what it meant was modifying the code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)