controls-web/controls-rework/docs/historian-readme.md

LASUCA Historian Notes

SQL Server History Database

• Server: 192.168.0.13\SQLEXPRESS
• Database: history
• Credentials: username opce, password opcelasuca

Core Tables

• dbo.archive
  • Columns: TimeStamp (datetime), ID (int), Value (float/numeric), Quality (int)
  • Holds raw samples from the PLC historian.
• dbo.id_names
  • Columns: idnumber (varchar), name (varchar), description (varchar)
  • Maps numeric IDs to human-readable tag names.

Joining Archive to Tag Metadata

id_names.idnumber is stored as text while archive.ID is an integer. Cast the idnumber column when joining:

FROM dbo.archive AS a
INNER JOIN dbo.id_names AS n
    ON a.ID = CAST(n.idnumber AS INT)

Trending endpoints such as trends/live/realtime_data.php rely on this join to look up tags by name:

$sql = "SELECT a.TimeStamp, a.Value
        FROM dbo.archive AS a
        INNER JOIN dbo.id_names AS n ON a.ID = CAST(n.idnumber AS INT)
        WHERE n.name = :tag_name";

Downtime View (Zero-Value Islands)

CREATE OR ALTER VIEW dbo.v_downtime_zero_segments AS
WITH ordered AS (
    SELECT
        a.ID,
        n.name,
        a.TimeStamp,
        a.Value,
        CASE
            WHEN a.Value = 0
             AND (LAG(a.Value) OVER (PARTITION BY a.ID ORDER BY a.TimeStamp) <> 0
                  OR LAG(a.Value) OVER (PARTITION BY a.ID ORDER BY a.TimeStamp) IS NULL)
            THEN 1 ELSE 0
        END AS start_flag,
        CASE
            WHEN a.Value = 0
             AND (LEAD(a.Value) OVER (PARTITION BY a.ID ORDER BY a.TimeStamp) <> 0
                  OR LEAD(a.Value) OVER (PARTITION BY a.ID ORDER BY a.TimeStamp) IS NULL)
            THEN LEAD(a.TimeStamp) OVER (PARTITION BY a.ID ORDER BY a.TimeStamp)
            ELSE NULL
        END AS next_good_timestamp
    FROM dbo.archive AS a
    INNER JOIN dbo.id_names AS n
        ON a.ID = CAST(n.idnumber AS INT)
),
grouped AS (
    SELECT
        *,
        SUM(start_flag) OVER (
            PARTITION BY ID
            ORDER BY TimeStamp
            ROWS UNBOUNDED PRECEDING
        ) AS group_id
    FROM ordered
    WHERE Value = 0
)
SELECT
    ID,
    name,
    MIN(TimeStamp) AS downtime_start,
    COALESCE(MAX(next_good_timestamp), MAX(TimeStamp)) AS downtime_end,
    DATEDIFF(SECOND, MIN(TimeStamp), COALESCE(MAX(next_good_timestamp), MAX(TimeStamp))) AS downtime_seconds
FROM grouped
GROUP BY ID, name, group_id;

Usage example (minimum 5 minutes):

SELECT *
FROM dbo.v_downtime_zero_segments
WHERE name IN ('MCCOUTPUT', 'TONS_PER_HOUR')
  AND downtime_start >= '2025-10-15T00:00:00'
  AND downtime_end   <= '2025-10-16T00:00:00'
  AND downtime_seconds >= 5 * 60
ORDER BY name, downtime_start;

This view expects SQL Server 2012 or later (window functions with LAG/LEAD).