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

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

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

Show all headers | View raw

On Tuesday, October 24, 2017 at 2:49:58 PM UTC-4, Erland Sommarskog wrote:
> Mike Arney (mikespcnet.net@gmail.com) writes:
> > 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 over the last 24
> > hours. 
> > 
> 
> Here is a query. However, it is clear to how you want to handle the case
> that the last row has Payload = 1. Should we count from that time until
> "now", or is there something else? In the example I am assuming "now",
> but you can change that as you see fit.
> 
> LEAD returns the next row in the result set as defined by the OVER clause.
> 
> ; WITH ontimes AS (
>     SELECT Topic, 
>            CASE WHEN Payload = 1
>                 THEN datediff(ss, Timestamp,
>                                   LEAD(Timestamp, 1, sysutcdatetime()) 
>                                      OVER (PARTITION BY Topic ORDER BY Timestamp))
>                 ELSE 0
>            END AS ontime
>     FROM  MQTTData
> )
> SELECT Topic, SUM(ontime) 
> FROM   ontimes
> GROUP  BY Topic
> 
> 
> 
> 
> 
> -- 
> Erland Sommarskog, Stockholm, esquel@sommarskog.se

Erland,
Thank you for this query! You are correct in assuming "NOW" as the count time. I trigger this query every 5 minutes, to populate a gauge on my UI. I have two gauges. One of them shows the "OnTime" for the last 24 hours and the other is the last 8 hours.

How can I get the last 24 hours of "OnTime" from "NOW"?

Thank you,
Mike Arney 

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