# Time Series Chart Question

**URL:** <https://forum.inductiveautomation.com/t/time-series-chart-question/97390>\
**Category:** Ignition\
**Tags:** ignition81, perspective\
**Created:** [October 17, 2024, 2:38pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-question/97390 "2024-10-17T14:38:16Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [October 17, 2024, 2:38pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-question/97390/1 "2024-10-17T14:38:16Z")

</div>

Good morning Team,

I am utilizing a db on our SQL server to record history from a query that is pulling data from the same database.

I have creatad the 2 tags and they start fresh each day at 5:30.

My issue revolves around displaying the data over 24 hours.

The data over 24 hours does not show properly. They do not show any record past 23:00 and do not start until 05:00.

I have rewritten the query a few times but the result is the same.

I ran the same data to list all the data points and they show up fine.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/e/6e802299686970e6dd8a422929b5ed2dea0f585a.png)

```python
SELECT 
    CAST((SUM(CASE WHEN Status = 1 THEN DATEDIFF(SECOND, t_stamp, ISNULL(Next_t_stamp, SYSDATETIME())) ELSE 0 END) * 100.0) / 
    SUM(DATEDIFF(SECOND, t_stamp, ISNULL(Next_t_stamp, SYSDATETIME()))) AS DECIMAL(5,2)) AS PercentageTimeInStatus1
FROM 
    (SELECT 
        Machine,
        Status,
        t_stamp,
        LEAD(t_stamp) OVER (PARTITION BY Machine ORDER BY t_stamp) AS Next_t_stamp
     FROM 
        [Ignitioncw].[dbo].[Machine_Statuses]
     WHERE 
        Machine = 'Palletizer 1'
        AND t_stamp >= DATEADD(MINUTE, 330, CONVERT(DATETIME, CONVERT(DATE, SYSDATETIME()))) -- Today 5:30 AM
        AND t_stamp < DATEADD(MINUTE, 330, DATEADD(DAY, 1, CONVERT(DATETIME, CONVERT(DATE, SYSDATETIME())))) -- Tomorrow 5:30 AM
    ) AS StatusDurations

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/3/53852bcbb26fad99f679af4834af3dad37939193.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 17, 2024, 3:17pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-question/97390/2 "2024-10-17T15:17:42Z")

</div>

The indented code may make it easier to see what's going on.

> **Indented query for legibility**
>
> ```python
> SELECT 
> CAST(
> (SUM(
> CASE 
> WHEN Status = 1 
> THEN 
> DATEDIFF(
> SECOND, 
> t_stamp, 
> ISNULL(Next_t_stamp, SYSDATETIME())
> ) 
> ELSE 0 
> END
> ) * 100.0) 
> / 
> SUM(
> DATEDIFF(
> SECOND, 
> t_stamp, 
> ISNULL(Next_t_stamp, SYSDATETIME())
> )
> ) AS DECIMAL(5,2)
> ) AS PercentageTimeInStatus1
> FROM 
> (SELECT 
> Machine,
> Status,
> t_stamp,
> LEAD(t_stamp) OVER (PARTITION BY Machine ORDER BY t_stamp) AS Next_t_stamp
> FROM 
> [Ignitioncw].[dbo].[Machine_Statuses]
> WHERE 
> Machine = 'Palletizer 1'
> AND t_stamp >= DATEADD(MINUTE, 330, CONVERT(DATETIME, CONVERT(DATE, SYSDATETIME()))) -- Today 5:30 AM
> AND t_stamp < DATEADD(MINUTE, 330, DATEADD(DAY, 1, CONVERT(DATETIME, CONVERT(DATE, SYSDATETIME())))) -- Tomorrow 5:30 AM
> ) AS StatusDurations
> 
> ```

I'm not clear what this query is doing. You're only returning one column, `PercentageTimeInStatus1`. Where are you using the result?

---

<div class="post-metadata">

**Author:** ![dominik.juhasz](https://avatars.discourse-cdn.com/v4/letter/d/e99b99/32.png) [@dominik.juhasz](https://forum.inductiveautomation.com/u/dominik.juhasz)\
**Post date:** [October 17, 2024, 3:19pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-question/97390/3 "2024-10-17T15:19:54Z")

</div>

Did you see all the missing / expacted rows in the database table?

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [October 17, 2024, 3:54pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-question/97390/4 "2024-10-17T15:54:20Z")

</div>

I am running a query against the PLC capture all of the statuses for the palletizer. So it looks at all of them and use total time in status 1 divided by the total to get a percentage uptime for that piece of equipment.

It starts at 5:30 so the operators can watch the uptime through out the day.

If i just run the data it shows all times for the 24 hour period but when I use it in the chart is where it does the gapping.

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [October 17, 2024, 3:54pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-question/97390/5 "2024-10-17T15:54:56Z")

</div>

yes they are there if i just run the data and not trying to parse it.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 17, 2024, 3:56pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-question/97390/6 "2024-10-17T15:56:14Z")

</div>

But for a Time Series chart wouldn't you have to return both the t\_stamp and value columns? You've only returned the values.

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [October 17, 2024, 4:06pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-question/97390/7 "2024-10-17T16:06:52Z")

</div>

I am not sure, but it works from 5am - 11 pm, why not after those hours?

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [October 17, 2024, 5:01pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-question/97390/8 "2024-10-17T17:01:31Z")

</div>

I confirmed that it is only showing the data from 5:30 am to 24:00.