I have a TimescaleDB ingesting electric meter data. The reported data does not align on my timezone daybreaks (PST) which is based on my browser. Below is the query:
SELECT
time_bucket_gapfill(‘1 day’::interval, time,
start => __timeFrom(),
finish => __timeTo()) AS “time”,
device,
interpolate(sum(“data”)/1440) AS “data”
FROM (
SELECT
time_bucket_gapfill(‘1 minute’::interval, d.event_time,
start => __timeFrom(),
finish => __timeTo()) AS “time”,
i.short_name AS “device”,
interpolate(avg(d.value)) AS “data”
FROM telemetry_numeric_data AS “d”, telemetry AS “t”, device AS “i”
WHERE t.telemetry_id = d.telemetry_id
AND $__timeFilter(d.event_time)
AND t.code = ‘pac’
AND i.device_id = t.device_id
AND i.device_type_id = (SELECT system_type_id FROM system_type WHERE type_context = ‘device.device_type_id’ AND code = ‘inverter’)
GROUP BY 1,2
ORDER BY 1,2
) AS data_series
GROUP BY 1,2
ORDER BY 1,2
Do I need to convert the event_time field into a local date/time?
Any help would be much appreciated.
Cheers,
-jm