Inconsistent query results postgress/timescale (timezones?)

Grafana Dashboards
1

I have room sensors that are populating a postgress 16.6/timesacale database (on docker compose on Rasp Pi 5) (1 is occupied, 0 is not occupied)

database
database416×278 10.2 KB

The timezone of the postgress database is UTC:
I use the latest grafana 11.4 with default time options (UTC)
And the time options in a dashboard : Browser Time

I have a query that is collecting the occupation in 5 min buckets from day start time to start lunch time and form end lunch time to the end of day time:

select time_bucket('5 minutes',time) as period,avg(bezetting::real) as bezet
from kantoorbezettingklim
where sensornaam='${sensor1}' and
((time > '${datumstart}'::timestamptz and time <'${datumeind}'::timestamptz) and
((time::time between '${tijdstipbegindag}'::time- interval '1' hour and '${tijdstipbeginlunch}'::time - interval '1' hour) or
(time::time between '${tijdstipeindelunch}'::time - interval '1' hour and '${tijdstipeindedag}'::time- interval '1' hour) and
(trim(TO_CHAR(time,'day'))<>'saturday' and trim(TO_CHAR(time,'day'))<>'sunday')))
group by period
order by period

The day start time is 8:30u. I have to correct the time with ‘- interval ‘1’hour’, to get the correct start time in the query result (starting at 8:30u).( In dbeaver I get the same results without the ‘- interval ‘1’hour’ correction)

result 1
result 1605×200 26.9 KB

But after several refreshes suddenly the start time is 07:30. From then on after a browser refresh the start time is fluctuationing from 8:30u to 7:30u (not regulary).

result2
result2605×309 39 KB

This looks it has to do with timezones and the behaviour is inconsistent. Is this a setting ? Is there a solution ?

Help would be appreciated

Thanks Hugo

2

Inconsistent query results in PostgreSQL/Timescale are often due to timezone handling issues. Ensure the server timezone, client timezone, and the timezone of your timestamps are consistent. Use AT TIME ZONE to explicitly set or convert timezones in queries. Additionally, verify if the database stores timestamps in UTC (TIMESTAMP WITH TIME ZONE) or local time (TIMESTAMP WITHOUT TIME ZONE) to avoid mismatches.

1 Like
3

The timezone in the time date column being in UTC or being converted to UTC via a function is sufficient

4

Both hostingraja and yosiasz thanks very much for the responses.

Now I set the postgres on UTC in the docker compose yaml file:

environment:

TZ: ‘UTC’
GTZ: ‘UTC’

All the dashboards are now on UTC.

Every query (mostly call to functions) are explicitly set with:

set timezone to ‘UTC’

This helped and now the results are consistent. :slight_smile:

Both thanks a lot

Hugo