Mysql to influx ql query from grafana

SELECT device_id as Sensor, ROUND(energy - CASE WHEN prev is NULL THEN CASE when cutoff is NULL THEN 0 ELSE cutoff END ELSE prev END,2) as energy, time

FROM(

SELECT sub.*, LAG(energy) OVER (PARTITION BY sub.device_id ORDER BY time ASC) AS prev, cutoff.last_energy as cutoff

FROM(

SELECT

Distinct(device_id) as device_id,

LAST_VALUE(energy) OVER (PARTITION BY device_id||‘-’|| CAST(DATE_TRUNC(‘day’, time) AS VARCHAR) ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS energy,

LAST_VALUE(DATE_TRUNC(‘day’, time)) OVER (PARTITION BY device_id||‘-’|| CAST(DATE_TRUNC(‘day’, time) AS VARCHAR) ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS time

FROM

$energy_measurement

where device_id in (${energy_devices}) and energy !=0 and time >= CAST(‘${start_date}’ AS timestamp)

) sub

Left Join

(SELECT

Distinct(device_id) as device_id,

LAST_VALUE(energy) OVER (PARTITION BY device_id ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_energy

FROM

$energy_measurement

WHERE

time <= CAST(‘${start_date}’ AS timestamp) and device_id in (${energy_devices})

) cutoff on sub.device_id=cutoff.device_id

) lagged

help me to convert this to influx ql

@pavbyte The short answer is you won’t be able to convert this query to InfluxQL. InfluxQL simply doesn’t offer the functionality that’s used in your MySQL query (Joins, date-formatting, casting, etc.).

What version of InfluxDB are you using? If you’re using InfluxDB Cloud Serverless, you may be able to do this with straight SQL.