Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31330

Re: How to add a datetime column for a 24 hour period

Show all headers | View raw

On Tuesday, October 24, 2017 at 8:10:10 AM UTC-4, Mike Arney wrote:
> On Monday, October 23, 2017 at 5:51:54 PM UTC-4, Erland Sommarskog wrote:
> > Mike Arney (mikespcnet.net@gmail.com) writes:
> > > I have a MSSQL table with the following columns and data types:
> > > 
> > > Topic -- nvarchar
> > > Payload -- numeric
> > > Timestamp -- datetime
> > > id -- int
> > > 
> > > 
> > > Here is some example data:
> > > 
> > > Topic                     Payload     Timestamp             id
> > > STG/A/CycleStart     0     2017-10-23 15:37:56.697     1651
> > > STG/A/CycleStart     0     2017-10-23 15:37:55.670     1650
> > > STG/A/CycleStart     0     2017-10-23 15:37:55.193     1649
> > > STG/A/CycleStart     0     2017-10-23 15:37:54.687     1648
> > > 
> > > 
> > > 
> > > 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.
> > > 
> > 
> > Too bad then that the sample data only has payload = 0.
> > 
> > The best way to get help with this type of question is to post
> > 1) CREATE TABLE statements for your table(s), preferrably simplified
> >    to focus at the problem at hand.
> > 2) INSERT statments with sample data.
> > 3) The expected results given the sample.
> > 4) A short description of the business rules that explains why you want
> >    that particular result.
> > 5) Which version of SQL Server you are using.
> > 
> > From the information you have posted so far, I am not prepare to make a
> > guess about what you are looking for.
> > 
> > -- 
> > Erland Sommarskog, Stockholm, esquel@sommarskog.se
> 
> Sorry Erland,
> 
> I will try to explain better.
> 
> [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)
> [/code]
> 
> I need to SUM all Timestamp (in seconds) with a Topic of 'STG/A/CycleStart', a Payload of 1.
> 
> Payload of 1 = Machine On
> Payload of 0 = Machine Off
> 
> I am trying to get amount of time that a Payload is = 1.
> 
> I am using SQL 2014 Enterprise.
> 
> Thanks,
> Mike

Forgot to show sample 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

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-10-23 11:42 -0700
  Re: How to add a datetime column for a 24 hour period Erland Sommarskog <esquel@sommarskog.se> - 2017-10-23 23:51 +0200
    Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-10-24 05:10 -0700
      Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-10-24 05:12 -0700
    Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-10-24 05:38 -0700
      Re: How to add a datetime column for a 24 hour period Erland Sommarskog <esquel@sommarskog.se> - 2017-10-24 20:49 +0200
        Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-10-25 04:57 -0700
          Re: How to add a datetime column for a 24 hour period Erland Sommarskog <esquel@sommarskog.se> - 2017-10-26 20:58 +0200
            Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-11-02 09:54 -0700
              Re: How to add a datetime column for a 24 hour period mikespcnet.net@gmail.com - 2018-05-08 08:21 -0700
                Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2018-05-08 08:35 -0700
                Re: How to add a datetime column for a 24 hour period Erland Sommarskog <esquel@sommarskog.se> - 2018-05-08 19:48 +0200
                Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2018-05-09 06:31 -0700
                Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2018-05-09 06:34 -0700
                Re: How to add a datetime column for a 24 hour period Erland Sommarskog <esquel@sommarskog.se> - 2018-05-09 23:08 +0200

csiph-web