Time Series Chart Question

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.

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

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

Indented query for legibility
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?

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

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.

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

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.

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

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