How to modify time intervals in SELECT query?


I have the query below supplied by another clever EE member.  It returns amounts for 10 second intervals.  My question is, if I want to change the interval to eg. 45 mins is it as simple as changing every "ss" to "mi" and every 10 to 45?

select convert(varchar(19),dateadd( ss, interval*10, CONVERT(int,getdate()) ), 121) as interval, amount
from (
 select DATEDIFF(ss,convert(int,getdate()),[timestamp])/10 as interval, SUM(amount) as amount
 from MyTable
 where [timestamp] >= convert(int,getdate())  -- this will limit the data to today's date, from midnight
 group by DATEDIFF(ss,convert(int,getdate()),[timestamp])/10

The original question is here if you want a description of the table involved:

Thanks in advance!
Who is Participating?

yes, but be aware that you are using the same interval for the sub query (10 sec.) maybe you need to correct it also to get the result you want.


No, mm stands for Month, use mi for minutes instead. Look here for the different values

DanziggerAuthor Commented:
Thanks - yeah it says "mi" in the post.

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I'm sorry, I read mi, and thought mm. Maybe I need more coffe?

Was that a help? Or do you have other questions regarding this problem?
Yes Danzigger,
You just have to change 'ss' to 'mi' and 10 to 45. That's it.
DanziggerAuthor Commented:
Ha, no worries pivar - we all need more coffee!  Thanks for looking at it.

It sounds like I've got my answer thanks guys.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.