none
Grouping Time stamps

    Question

  • Hello,

    This might be a simple question, but my knowledge about SQL-language is not good.

     

    I have a table of following syntax:

    Time_Stamp

    Column1

    Column2

    2008-08-19 14:21:30.000

    3.4

    1.6

    2008-08-19 14:21:31.000

    3.4

    1.6

    2008-08-19 14:21:32.000

    3.4

    1.6

    2008-08-19 14:21:33.000

    7

    1.6

    2008-08-19 14:21:34.000

    7

    1.6

    2008-08-19 14:21:35.000

    2

    1.6

     

    There are one time-stamp each second for a long time.

     

    What I want to do is to group lets say 3 and 3 points and display the average of those 3 points. If I take the table above as an example, I want the result to be as following:

    from_unixtime(avg(unix_timestamp(Time_Stamp )))

    avg(Column1)

    avg(Column2)

    2008-08-19 14:21:31.000

    3.4

    1.6

    2008-08-19 14:21:34.000

    5.33

    1.6

     

     

    I have already the syntax ready for MySQL, but I cannot get it working with SQL Server 2005.

    Following is my syntax in MySQL (

     

    SELECT from_unixtime(avg(unix_timestamp(Time_Stamp ))), avg(Column1), avg(Column2) FROM Table01 WHERE Time_Stamp BETWEEN cast('2008-08-19 14:21:30' as datetime) AND date_add(cast('2008-08-19 14:21:30' as datetime),interval 6 Second) group by floor(TIMESTAMPDIFF(Second,'2008-08-19 14:21:30',Time_Stamp)/3)

     

    Anyone having an idea of getting this to work using SQL Server 2005?

     

    Thanks,

    Trond

    Wednesday, August 27, 2008 10:16 AM

Answers

  • And like this:

     

    Code Snippet

    SELECT cast(avg(cast(Time_Stamp as float)) as datetime), avg(Column1), avg(Column2) FROM Table01 WHERE Time_Stamp BETWEEN '2008-08-19 14:21:30' AND '2008-08-19 14:21:36' group by

    floor(DateDiff(ss,'2008-08-19 14:21:30',Time_Stamp)/3)

     

     

    Wednesday, August 27, 2008 2:46 PM
  •  

    Not sure what exactly you are looking for,

    but you can try something like this

    Code Snippet

     

    SELECT cast(avg(cast(Time_Stamp as float)) as datetime), avg(Column1), avg(Column2)

    INTO newTable

    FROM Table01

    WHERE Time_Stamp BETWEEN '2008-09-05' AND '2008-09-06'

    GROUP BY (DATEDIFF(ss,'2008-09-05 00:00:00',Time_Stamp)/600)

     

     

    Tuesday, September 30, 2008 9:51 AM
    Moderator

All replies

  • Try like this:

     

     

    Code Snippet

    SELECT avg(Time_Stamp), avg(Column1), avg(Column2) FROM Table01 WHERE Time_Stamp BETWEEN '2008-08-19 14:21:30' AND '2008-08-19 14:21:36' group by

    floor(DateDiff(ss,'2008-08-19 14:21:30',Time_Stamp)/3)

     

     

    Wednesday, August 27, 2008 12:13 PM
  • Thanks for the answer,

     

    (By the way, Column 'Time_Stamp' is the type datetime, and that format did not allow avg() so I removed the avg().

     

    But it did not work, I got following error-message:

     

    Msg 8120, Level 16, State 1, Line 1

    Column 'Table01.Time_Stamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

     

    It seems like I need to have equal statement in the Select and Group By, am I correct?

    If I do it like below, I get the average of Column1 and Column2 presented, but I am not getting the correct time_stamp.

     

    SELECT floor(DateDiff(ss,'2008-08-19 14:21:30',Time_Stamp)/3), avg(Column1), avg(Column2) FROM Table01 WHERE Time_Stamp BETWEEN '2008-08-19 14:21:30' AND '2008-08-19 14:21:36' group by

    floor(DateDiff(ss,'2008-08-19 14:21:30',Time_Stamp)/3)

     

    Any suggestions om how to get the Time_Stamp presented as well?

    Thanks

    Wednesday, August 27, 2008 2:30 PM
  • And like this:

     

    Code Snippet

    SELECT cast(avg(cast(Time_Stamp as float)) as datetime), avg(Column1), avg(Column2) FROM Table01 WHERE Time_Stamp BETWEEN '2008-08-19 14:21:30' AND '2008-08-19 14:21:36' group by

    floor(DateDiff(ss,'2008-08-19 14:21:30',Time_Stamp)/3)

     

     

    Wednesday, August 27, 2008 2:46 PM
  • Sorry for my delay, but thank you for the answer.

     

    I ended up with this statement:

    SELECT cast(avg(cast(Time_Stamp as float)) as datetime), avg(Column1), avg(Column2) FROM Table01 WHERE Time_Stamp BETWEEN '2008-09-06 01:00:00' AND dateadd(ss,540, '2008-09-06 01:00:00')GROUP BY (DATEDIFF(ss,'2008-09-06 01:00:00',Time_Stamp)/3) ORDER BY (DATEDIFF(ss,'2008-09-06 01:00:00',Time_Stamp)/3)

     

    I am now facing another problem:

    I have this table with samples every second for about 200 analog values. Every day I want to copy the samples for one day and store them in another table, but I don't want all samples. I am thinking of either gather 600 samples (10 minutes) into one average point or select one point with 600s interval and copy it into the new table.

    Anyone having an idea?

     

    I tried with the following:

     

    SELECT * into newTable FROM Table 01 WHERE Time_Stamp BETWEEN '2008-09-05' AND '2008-09-06')

     

     

    But this statement copy all samples (since I don't have any other GROUP BY or something like that.)

    If I try with the same Group By clause as above:

     

    SELECT * into newTable FROM Table01 WHERE Time_Stamp BETWEEN '2008-09-05' AND '2008-09-06' GROUP BY (DATEDIFF(ss,'2008-09-05 00:00:00',Time_Stamp)/600)

     

    I got an error-message saying following:

    Msg 8120, Level 16, State 1, Line 1

    Column 'Table01.Time_Stamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

     

     

     

    Any help with this problem is appreciated

    (By the way, I am using SQL Server Express 2005 SP2 for testing, and is going to use SQL Server 2005 SP2 on the final installation)

    Thanks,

    Trond

    Tuesday, September 30, 2008 9:24 AM
  •  

    If you are doing group by it is better to list column names in select clause and add appropriate grouping functions to columns.

    Don't use select *

    Tuesday, September 30, 2008 9:27 AM
    Moderator
  • OK,

    Is there any other statements than group by that can be used for this issue?

     

    Tuesday, September 30, 2008 9:42 AM
  •  

    Not sure what exactly you are looking for,

    but you can try something like this

    Code Snippet

     

    SELECT cast(avg(cast(Time_Stamp as float)) as datetime), avg(Column1), avg(Column2)

    INTO newTable

    FROM Table01

    WHERE Time_Stamp BETWEEN '2008-09-05' AND '2008-09-06'

    GROUP BY (DATEDIFF(ss,'2008-09-05 00:00:00',Time_Stamp)/600)

     

     

    Tuesday, September 30, 2008 9:51 AM
    Moderator
  • I have a problem thats a bit related to the question in this thread ...

     

    My old MySQL-Syntax asks for a time-difference between actual time and the last recorded time in a specific column.

    SELECT (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(zeit)) as time, zeit FROM users WHERE username = <user>

    after migrating to SQL Server 2008 R2 I get an error-message " 'UNIX_TIMESTAMP' is not a recognized built-in function name."

    but i want the seconds (hence the Unix-timestamp-function) beween the actual date&time and the date & time in the "zeit" column ( a smalldatetime-column in SQL Server 2008 R2)

    Can you tell me a working solution for my SQL-Statement ?

    thanks for your Support


    Friday, September 30, 2011 10:53 AM
  • You have to use the DATEDIFF function.

    Datetime conversion article:

    http://www.sqlusa.com/bestpractices/datetimeconversion/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Friday, September 30, 2011 11:39 AM
    Moderator