Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unanswered: Database timestamp query

    Hi,

    Ive got a load of data in a database that is collected from a utility monitoring device. Therefore every 30 seconds a new data value is added with a timestamp.

    I want to query the database to return values from a user specified amount of time, ie. for every 30minutes, 60minutes, 24 hours etc. For example the data in the database could be: -

    TIMESTAMP-----------------------VALUE
    16/09/2009 10:00:30------------13
    16/09/2009 10:01:00------------15
    16/09/2009 10:01:30------------11
    16/09/2009 10:02:00------------14
    16/09/2009 10:02:30------------13
    16/09/2009 10:03:00------------9


    And I would want to retrieve: -

    TIMESTAMP-----------------------VALUE
    16/09/2009 10:01:00------------28
    16/09/2009 10:02:00------------25
    16/09/2009 10:03:00------------22

    Hope this is clear,
    Thanks,
    Mark

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    drop TABLE daTable
    
    CREATE TABLE daTable(
    	id	INT		IDENTITY(1,1)	NOT NULL,
    	TS	DATETIME	NOT NULL,
    	val	INT		NOT NULL,
    	CONSTRAINT pk_daTable PRIMARY KEY (id)	
    )
    
    -- run the INSERT statement a number of times, allow some time between runs (or insert some literal values)
    INSERT INTO daTable (TS, val) VALUES(GetDate(), datediff(s, dateadd(dd,0, datediff(dd,0,GetDate())), GetDate()));
    select * from daTable;
    
    with cte as (
    SELECT ROW_NUMBER() OVER(PARTITION BY datediff(mi, dateadd(dd,0, datediff(dd,0,TS)), TS) ORDER BY TS DESC) as RowNum,
    	TS,
    	Val
    FROM  daTable
    )
    SELECT	TS,
    		Val
    FROM cte
    WHERE RowNum = 1 AND
    	datediff(mi, dateadd(dd,0, datediff(dd,0,TS)), TS) % 3 = 0
    datediff(mi, dateadd(dd,0, datediff(dd,0,TS)), TS) gives you the number of minutes since midnight.
    Change the 3 in any other value (eg 30) to have results every (eg 30) minutes apart.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Hmm, I just reread your problem, you want VALUE to be the sum of all the values in the interval. My solution doesn't do that. It shows the latest VALUE in the interval.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT truncated_hour
         , Sum(value)
    FROM   (
            SELECT timestamp
                 , DateAdd(hh, DateDiff(hh, 0, timestamp), 0) As truncated_hour
                 , value
            FROM   your_table
           ) As x
    To the nearest hour
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    I would have thought that you needed to divide the times by the interval the Ceiling them and group on the new field

    Code:
    SELECT     MAX(TimeStamp) AS TimeStamp, SUM(value) AS Val
    FROM        YourTable
    GROUP BY CEILING(CONVERT(float, TimeStamp) / 0.25)
    that i think groups into 6 hour periods ie 6 hour = .25 days


    EDIT
    swap the MAX(TimeStamp)for
    CONVERT(datetime,CEILING(CONVERT(float, MAX(TimeStamp)) / 0.25)*0.25)
    to round the them to
    and replace the 0.25 with a parameter an your can feed that in to the query as well just take the time-period in question and convert to float
    Last edited by m.timoney; 09-17-09 at 09:59.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select dateadd(mi,datediff(mi,'', dateadd(ss,59,timestamp)),''), sum(value)
    from TheTable
    group by dateadd(mi,datediff(mi,'', dateadd(ss,59,timestamp)),'')

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by pdreyer
    Code:
    select dateadd(mi,datediff(mi,'', dateadd(ss,59,timestamp)),''), sum(value)
    from TheTable
    group by dateadd(mi,datediff(mi,'', dateadd(ss,59,timestamp)),'')
    are you sure about that one?
    i've given it a quick test and it doesn't produce the results i would expect,
    it was only a very quick test
    Last edited by m.timoney; 09-17-09 at 10:36.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    It produces the results desired by the op
    Code:
    select dateadd(mi,datediff(mi,'', dateadd(ss,59,timestamp)),''), sum(value)
    from (select -- test data --
    '20090916 10:00:30', 13    union all select
    '20090916 10:01:00', 15    union all select
    '20090916 10:01:30', 11    union all select
    '20090916 10:02:00', 14    union all select
    '20090916 10:02:30', 13    union all select
    '20090916 10:03:00',  9   )testdata(
    timestamp          , value)
    group by dateadd(mi,datediff(mi,'', dateadd(ss,59,timestamp)),'')
    
    ------------------- --------------
    2009-09-16 10:01:00             28
    2009-09-16 10:02:00             25
    2009-09-16 10:03:00             22

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    ah i made a mistake on the test nevermind

    when i use your test data it matched
    Code:
    Declare @period Float
    Set @period = Convert(Float,convert(Datetime,'00:01:00'))
    
    SELECT     MAX([TimeStamp]) AS TimeStamp, SUM(value) AS Val
    FROM        (select -- test data --
    convert(Datetime,'20090916 10:00:30') , 13   union all select
    convert(Datetime,'20090916 10:01:00'), 15    union all select
    convert(Datetime,'20090916 10:01:30'), 11    union all select
    convert(Datetime,'20090916 10:02:00'), 14    union all select
    convert(Datetime,'20090916 10:02:30'), 13    union all select
    convert(Datetime,'20090916 10:03:00'),  9   )testdata(
    timestamp          , value)
    
    GROUP BY CEILING(CONVERT(float, [TimeStamp]) /@period)
    
    2009-09-16 10:01:00.000	28
    2009-09-16 10:02:00.000	25
    2009-09-16 10:03:00.000	22
    i forgot to match the time periods which is where i went wrong
    Last edited by m.timoney; 09-17-09 at 12:36.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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