Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: GROUP BY minute?

    Is there a simpler way to group an SQL query by minute?

    SELECT
    datepart(year, DateTimeColumn)
    datepart(month, DateTimeColumn)
    datepart(day, DateTimeColumn)
    datepart(hour, DateTimeColumn)
    datepart(minute, DateTimeColumn)
    FROM SomeTable GROUP BY
    datepart(year, DateTimeColumn)
    datepart(month, DateTimeColumn)
    datepart(day, DateTimeColumn)
    datepart(hour, DateTimeColumn)
    datepart(minute, DateTimeColumn)

    How could I get the result back as a datetime rather than 5 separate date related columns?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SELECT cast(convert(varchar(16), getdate(), 120) as datetime)
    FROM YourTable
    GROUP BY convert(varchar(16), DateTimeColumn, 120)

    blindman

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This takes a minute and half to run, depending on the box

    What about..

    Code:
    USE Northwind
    GO
    SET NOCOUNT ON
    
    DECLARE @myTable99 TABLE (Col1 int IDENTITY(1,1), Col2 datetime DEFAULT GetDate(), Col3 char(1))
    
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < 1000000
    	BEGIN
    		INSERT INTO @myTable99 (Col3) SELECT 'x'
    		SELECT @i = @i + 1
    	END
    
    SET NOCOUNT OFF
    
    SELECT CONVERT(varchar(26),Col2,101), DATEPART(n,Col2), COUNT(*)
      FROM @myTable99
    GROUP BY  CONVERT(varchar(26),Col2,101), DATEPART(n,Col2)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    Originally posted by Brett Kaiser
    This takes a minute and half to run, depending on the box

    What about..
    That won't work. It will separate between different dates but not different hours; 3:31 and 4:31 will be grouped together. Try the following which does a few manual INSERTS.

    Code:
    USE Northwind
    GO
    SET NOCOUNT ON
    
    DECLARE @myTable99 TABLE (Col1 int IDENTITY(1,1), Col2 datetime DEFAULT GetDate(), Col3 char(1))
    
    INSERT INTO @myTable99 (Col2, Col3) VALUES (dateadd(minute, 0, getdate()), 'x')
    INSERT INTO @myTable99 (Col2, Col3) VALUES (dateadd(minute, 0, getdate()), 'x')
    INSERT INTO @myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
    INSERT INTO @myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
    INSERT INTO @myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
    INSERT INTO @myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
    INSERT INTO @myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')
    INSERT INTO @myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')
    INSERT INTO @myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')
    INSERT INTO @myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')
    
    SET NOCOUNT OFF
    
    SELECT CONVERT(varchar(26),Col2,101), DATEPART(n,Col2), COUNT(*)
      FROM @myTable99
    GROUP BY  CONVERT(varchar(26),Col2,101), DATEPART(n,Col2)

  5. #5
    Join Date
    Oct 2003
    Posts
    268
    Originally posted by blindman
    SELECT cast(convert(varchar(16), getdate(), 120) as datetime)
    FROM YourTable
    GROUP BY convert(varchar(16), DateTimeColumn, 120)

    blindman
    I had to add the cast in the group by section as well but then it works perfectly! Thanks!

    Code:
    USE Northwind
    GO
    
    DECLARE @sampleTable TABLE (IdColumn int IDENTITY(1,1), DateColumn datetime DEFAULT GetDate(), DataColumn char(1))
    
    INSERT INTO @sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 0, getdate()), 'x')
    INSERT INTO @sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 0, getdate()), 'x')
    INSERT INTO @sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
    INSERT INTO @sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
    INSERT INTO @sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
    INSERT INTO @sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
    INSERT INTO @sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')
    INSERT INTO @sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')
    INSERT INTO @sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')
    INSERT INTO @sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')
    
    SELECT COUNT(*), CAST(CONVERT(varchar(16), DateColumn, 120) AS datetime)
    FROM @sampleTable
    GROUP BY CAST(CONVERT(varchar(16), DateColumn, 120) AS datetime)

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Good point...how about

    Code:
    USE Northwind
    GO
    SET NOCOUNT ON
    
    DECLARE @myTable99 TABLE (Col1 int IDENTITY(1,1), Col2 datetime DEFAULT GetDate(), Col3 char(1))
    
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < 1000000
    	BEGIN
    		INSERT INTO @myTable99 (Col3) SELECT 'x'
    		SELECT @i = @i + 1
    	END
    
    SET NOCOUNT OFF
    
    SELECT SUBSTRING(CONVERT(varchar(26),Col2,120),1,13), DATEPART(n,Col2), COUNT(*)
      FROM @myTable99
    GROUP BY  SUBSTRING(CONVERT(varchar(26),Col2,120),1,13), DATEPART(n,Col2)
    Returns:

    ------------- ----------- -----------
    2003-11-11 15 2 365972
    2003-11-11 15 3 634028

    (2 row(s) affected)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •