Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2017
    Posts
    1

    Unanswered: How to add a datetime column over the last 24 hour period?

    I have a MSSQL table with the following columns and data types:

    Topic -- nvarchar
    Payload -- numeric
    Timestamp -- datetime
    id -- int

    Here is SQL Create Table and Insert Data Code:

    Code:
    Create Table MQTTData(
    	Topic nvarchar(256),
    	Payload numeric(18,0),
    	Timestamp datetime Default GetUTCDate(),
    	id int Identity(1,1) Primary Key)
    
    	Insert Into MQTTData (Topic,Payload)
    	Values ('STG/A/CycleStart', 1)
    
    	WaitFor Delay '00:00:02'
    
    	Insert Into MQTTData (Topic,Payload)
    	Values ('STG/A/CycleStart', 0)
    
    	WaitFor Delay '00:00:02'
    
    	Insert Into MQTTData (Topic,Payload)
    	Values ('STG/A/CycleStart', 1)
    
    	WaitFor Delay '00:00:02'
    
    	Insert Into MQTTData (Topic,Payload)
    	Values ('STG/A/CycleStart', 0)
    
    	WaitFor Delay '00:00:02'
    
    	Insert Into MQTTData (Topic,Payload)
    	Values ('STG/A/CycleStart', 1)
    
    	WaitFor Delay '00:00:02'
    
    	Insert Into MQTTData (Topic,Payload)
    	Values ('STG/A/CycleStart', 0)
    
    	WaitFor Delay '00:00:02'
    
    	Insert Into MQTTData (Topic,Payload)
    	Values ('STG/A/CycleStart', 1)
    Here is some example data:

    Topic Payload Timestamp id
    STG/A/CycleStart 1 2017-10-24 12:00:37.160 1
    STG/A/CycleStart 0 2017-10-24 12:00:39.160 2
    STG/A/CycleStart 1 2017-10-24 12:00:41.160 3
    STG/A/CycleStart 0 2017-10-24 12:00:43.160 4
    STG/A/CycleStart 1 2017-10-24 12:00:45.163 5
    STG/A/CycleStart 0 2017-10-24 12:00:47.167 6
    STG/A/CycleStart 1 2017-10-24 12:00:49.167 7

    The Timestamp column's default value is set to: (getutcdate())


    I need to add the TIMESTAMP column for the last 24 hours where TOPIC = 'STG/A/CycleStart' and PAYLOAD = 1

    I also need this time in seconds.

    Any help would be greatly appreciated.

    Thanks,
    Mike
    Last edited by arneym; 10-24-17 at 09:33.

Tags for this Thread

Posting Permissions

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