Skip to content

Common queries¶

Find signal id by asset name, hardpoint name, and signal name¶

SELECT s.id
FROM assets a
  JOIN hardpoints h on a.id = h.asset_id
  JOIN signals s on h.id = s.hardpoint_id
WHERE a.name = $1
  AND h.name = $2
  AND s.name = $3

$1 is name of asset
$2 is name of hardpoint
$3 is name of signal

Find signal id by device serial number and signal name¶

SELECT s.id
FROM devices d
  JOIN signals s ON d.id = s.device_id
WHERE d.serial_number = $1
  AND s.name = $2

$1 is device serial number
$2 is signal name

Get latest value for a signal¶

SELECT sd.t, sd.y
FROM signal_data sd
WHERE sd.signal_id = $1
ORDER BY sd.t DESC
LIMIT 1

$1 is the signal id

Signal data for a time range¶

SELECT t, y
FROM signal_data
WHERE signal_id = $1
    AND t BETWEEN $2 AND $3
ORDER BY t

$1 is the signal id
$2 and $3 are the from and to timestamps, respectively

Signal rate of change for a time range¶

SELECT s.t,
    CAST((lead(s.y) OVER w1 - lag(s.y) OVER w1) / EXTRACT( epoch FROM lead(s.t) OVER w1 - lag(s.t) OVER w1) AS REAL) AS y
  FROM signal_data s
  WHERE s.signal_id = $1 AND s.t BETWEEN $2 AND $3
  WINDOW w1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW)
  ORDER BY s.t

$1 is the signal id
$2 and $3 are the from and to timestamps, respectively

List of all signals with latest readings¶

SELECT a.name, h.name, s.name, sd.t, sd.y
FROM assets a
JOIN hardpoints h ON a.id = h.asset_id
JOIN signals s ON h.id = s.hardpoint_id
JOIN LATERAL (
    SELECT x.t, x.y
    FROM signal_data x
    WHERE x.signal_id = s.id
    ORDER BY x.t DESC
    LIMIT 1
) sd ON true