search

openremote / openremote

100% open-source IoT Platform - Integrate your devices, create rules, and analyse and visualise your data
https://openremote.io
Other
1.24k stars 299 forks source link

Fix broken AssetDatapointQueryTest #1288

Open richturner opened 6 months ago

richturner commented 6 months ago

The Test lttb datapoint query in AssetDatapointQueryTest is broken and it seems to be time zone related.

On upsert timestamps seem to be correctly set to UTC time.

But on query the requested from/to time is wrong, here is value of PreparedStatement generated and you can see zoned time strings which are then cast to timestamp (which is the column type) but by doing this the timezone is truncated so the query executes using the wrong time:

HikariProxyPreparedStatement@1050581728 wrapping select * from public.unnest((select public.lttb(timestamp::timestamptz, value::double precision, 5) from asset_datapoint where ENTITY_ID = '2HpegXUAjpcJWNQkRX45nz' and ATTRIBUTE_NAME = 'brightness' and TIMESTAMP >= '2024-04-10 16:37:40.085474+01'::timestamp and TIMESTAMP <= '2024-04-10 17:07:40.085474+01'::timestamp))

See this pgjdbc issue which gives some context, looks like they may have made a change which has broken things for us once the pgjdbc driver was updated:

https://github.com/pgjdbc/pgjdbc/issues/2850

Using a timestamp without time zone column in postgres is fine as long as we make sure we always insert/query data in UTC time, as described above this is not happening.

MartinaeyNL commented 6 months ago

Will try to investigate this, and identify the issue with the from/to timestamps.

A bit unrelated, but still interesting to note, is that the lttb function from Timescale uses timezones in their parameters; https://docs.timescale.com/api/latest/hyperfunctions/downsampling#lttb

lttb(
  ts TIMESTAMPTZ,
  value DOUBLE PRECISION,
  resolution INT
) RETURNS Timevector

(for context; haven't done any investigation on the issue yet)