X-Received: by 10.200.8.79 with SMTP id x15mr12944344qth.35.1508847177599; Tue, 24 Oct 2017 05:12:57 -0700 (PDT) X-Received: by 10.31.69.204 with SMTP id s195mr1210155vka.12.1508847177566; Tue, 24 Oct 2017 05:12:57 -0700 (PDT) Path: csiph.com!xmission!news.snarked.org!border2.nntp.dca1.giganews.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!z50no1496062qtj.0!news-out.google.com!v14ni1530qtc.0!nntp.google.com!z50no1496061qtj.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: microsoft.public.sqlserver.programming Date: Tue, 24 Oct 2017 05:12:57 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=216.234.125.194; posting-account=4nS5WwoAAACio8l9etd0ayIsc32V0GtK NNTP-Posting-Host: 216.234.125.194 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <7ca7e4e7-3b65-4eee-8982-00ddb4096e40@googlegroups.com> Subject: Re: How to add a datetime column for a 24 hour period From: Mike Arney Injection-Date: Tue, 24 Oct 2017 12:12:57 +0000 Content-Type: text/plain; charset="UTF-8" Lines: 113 Xref: csiph.com microsoft.public.sqlserver.programming:31330 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