How to modify time intervals in SELECT query?

Posted on 2011-10-17
Last Modified: 2012-08-13

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!
Question by:Danzigger
    LVL 22

    Expert Comment


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


    Author Comment

    Thanks - yeah it says "mi" in the post.

    LVL 22

    Expert Comment

    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?

    Accepted Solution


    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.

    LVL 8

    Assisted Solution

    Yes Danzigger,
    You just have to change 'ss' to 'mi' and 10 to 45. That's it.

    Author Comment

    Ha, no worries pivar - we all need more coffee!  Thanks for looking at it.

    It sounds like I've got my answer thanks guys.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now