Mentat

This bug is more serious and should be resolved once and for all. There is a confusion in handling times passed down to and from forms.

Attached patches should resolve this issue once and for all. Pavel, please help me verify, that it is actually so. Currently the code is deployed on our mentat-alt development server:

https://mentat-alt.cesnet.cz/mentat/events/search

Czech locale, timezone explicitly set at user account to Europe/Prague.

Entering range 2019-07-22 13:00:00 and 2019-07-22 14:00:00 on events/search.

No results, ranges on result page switch to 2019-07-22 13:00:00 and 2019-07-22 14:20:56 respectively.

That does not look good.

Pavel Kácha wrote:

Czech locale, timezone explicitly set at user account to Europe/Prague.

Entering range 2019-07-22 13:00:00 and 2019-07-22 14:00:00 on events/search.

No results, ranges on result page switch to 2019-07-22 13:00:00 and 2019-07-22 14:20:56 respectively.

That does not look good.

Hi, Pavel, thank you for the feedback, however I am not able to reproduce your issue. Are you testing this on mentat-alt? Could you please provide direct link to the example wrong search page?

Nevertheless I think I have just found the root of our timestamp mess. Our timestamp columns have the timestamp psql datatype. However when python datetime object with timezone is provided, the psycopg2 driver uses ::timestamptz psql conversion and search does not match correctly, for example:

root@mentat-alt:~# psql mentat_events 
psql (11.4 (Debian 11.4-1.pgdg90+1))
Type "help" for help.

mentat_events=# select id, detecttime from events where id='8b425b00-88da-41ab-9372-85cc0bbd85b6';
                  id                  |     detecttime      
--------------------------------------+---------------------
 8b425b00-88da-41ab-9372-85cc0bbd85b6 | 2019-07-29 08:55:40
(1 row)

mentat_events=# select id, detecttime from events where id='8b425b00-88da-41ab-9372-85cc0bbd85b6' and detecttime >= '2019-07-29T08:00:00+00:00'::timestamptz;
 id | detecttime 
----+------------
(0 rows)

mentat_events=# select id, detecttime from events where id='8b425b00-88da-41ab-9372-85cc0bbd85b6' and detecttime >= '2019-07-29T08:00:00+00:00'::timestamp;
                  id                  |     detecttime      
--------------------------------------+---------------------
 8b425b00-88da-41ab-9372-85cc0bbd85b6 | 2019-07-29 08:55:40
(1 row)

mentat_events=# select id, detecttime from events where id='8b425b00-88da-41ab-9372-85cc0bbd85b6' and detecttime >= '2019-07-29T08:00:00'::timestamp;
                  id                  |     detecttime      
--------------------------------------+---------------------
 8b425b00-88da-41ab-9372-85cc0bbd85b6 | 2019-07-29 08:55:40
(1 row)

The generated SQL query looks like this:

SELECT "id", "detecttime", "category", "description", "source_ip", "target_ip", "source_port", "target_port", "source_type", "target_type", "protocol", "node_name", "node_type", "cesnet_resolvedabuses", "cesnet_storagetime", "cesnet_eventclass", "cesnet_eventseverity", "cesnet_inspectionerrors", "event" FROM events WHERE "detecttime" >= '2019-07-29T08:00:00+00:00'::timestamptz ORDER BY "detecttime" DESC LIMIT 100

So the current state is, that input from the user is correctly recognized and converted to datetime object in UTC with timezone. However the timezone awareness causes problems, because database itself uses naive datetime objects that are in UTC and the psycopg2 driver attempts to do the smart thing and the search fails.

I would like to brainstorm with you guys how best to solve this once and for all. I have read somewhere, that it is best not to use timezone aware timestamps in database, so I guess that changing the datatype is not the correct option. I can strip the timezone from the datetime object before building the SQL query, so the psycopg2 driver should use the ::timestamp conversion instead. Or there may be some option to enforce this in the query?

Radko Krkoš wrote:

The advice about using exclusively timestamps without timezone information was given at P2D2 lecture this year.
As for the problem, the timezone information has to be supplied by Hawat as we obviously support choosing timezone for users. So changing the query would be possible, but hard to make right and in effect fragile. It is best to leave the timezone manipulation to postgres. As for the timestamps in the database, all seem to be in UTC, without time zone information. The problem is that the server (at least on mentat-alt) was set up that the time zone used was local - Europe/Prague, so timestamps were numerically in UTC, but the server represented them as in the Prague timezone.
I have changed the setting to UTC in postgresql.conf:
[...]
This should fix the problem, seems correct based on my limited testing. As you two have dealt with the issue for a long time (and as resetting the preferred timezone in Hawat keeps the last time zone), please test this. If this is indeed a good fix, we can document it and deploy to mentat-hub.

Hi, I think that this could be the final solution to our datetime hell. After your config change the event search form seems to finally behave, however the dashboards are now broken (they use sqlalchemy). Going to fix them now.

Everything seems to work.

No more feedback, I now consider this issue as closed.