Bug #7631
closedTimestamp bounds in event search work badly with timezones
100%
Description
When user timezone is Europe/Prague, search results are 1 or 2 hours off. Most probably wrongly (or not) interpreted timezone.
Files
Updated by Rajmund Hruška over 1 year ago
- Status changed from New to In Progress
I think I know where is the issue here.
What seemed strange to me is that no matter in what timezone the user is, the results are always one hour off. I checked the query:
SELECT * FROM events AS "_mentatq(148_wnbqzt)_" WHERE "detecttime" >= '2023-03-07T02:59:55+00:00'::timestamptz AND "detecttime" <= '2023-03-07T03:00:00+00:00'::timestamptz ORDER BY "detecttime" DESC LIMIT 100
and that's when I realised that the issue is in this query. If the query has timestamp instead of timestamptz or 2023-03-07T02:59:55+01:00 instead of 2023-03-07T02:59:55+00:00, we get the correct result. The timezone in Postgresql is set to localtime.
I don't know how to change the query yet.
Updated by Rajmund Hruška over 1 year ago
The other option is to set the timezone in Postgresql to UTC, but I am not sure of the consequences.
Updated by Rajmund Hruška over 1 year ago
- Status changed from In Progress to Resolved
- Target version changed from Backlog to 2.11
- % Done changed from 0 to 100
It should be fixed in 94e0e44f.
Updated by Rajmund Hruška over 1 year ago
- Status changed from Resolved to In Review
Merged into devel and deployed on mentat-alt.
Updated by Rajmund Hruška over 1 year ago
- Status changed from In Review to Closed
Updated by Rajmund Hruška over 1 year ago
- Status changed from Closed to In Progress
- % Done changed from 100 to 50
The issue is still present when using quicksearch for last X hours.
Updated by Rajmund Hruška over 1 year ago
- Status changed from In Progress to Closed
- % Done changed from 50 to 100