Project

General

Profile

Actions

Bug #4518

closed

Inconsistent time ranges in dashboard

Added by Pavel Kácha over 3 years ago. Updated about 3 years ago.

Status:
Closed
Priority:
High
Assignee:
Category:
Development - GUI
Target version:
Start date:
01/14/2019
Due date:
% Done:

100%

Estimated time:
To be discussed:

Description

In both dashboards shown time is one hour greater than selected time.

If I choose From: 2019-01-14 01:00, interval of the result is shown starting from 14. 1. 2019 2:00:00.

Timezone issue? Internationalisation issue? Off-by-one error?

Times manipulated and seen by user within web interface (except raw data dumps and maybe some loudly communicated exceptions) should be in local (or user selected) timezone.

Please, review also whether this issue does not apply to some other forms. Seems to me like alert query is also running in GMT timezone, not local one.

Actions #1

Updated by Jan Mach over 3 years ago

  • Priority changed from Normal to High
  • Target version changed from Backlog to 2.4

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.

Actions #2

Updated by Jan Mach over 3 years ago

  • Target version changed from 2.4 to 2.5
Actions #3

Updated by Jan Mach about 3 years ago

  • Status changed from New to Feedback
  • Assignee changed from Jan Mach to Pavel Kácha
  • % Done changed from 0 to 100

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

Actions #4

Updated by Pavel Kácha about 3 years ago

  • Assignee changed from Pavel Kácha to Jan Mach

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.

Actions #5

Updated by Jan Mach about 3 years ago

  • Assignee changed from Jan Mach to Radko Krkoš

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?

Actions #6

Updated by Radko Krkoš about 3 years ago

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:

# CHANGED BY RADKO FROM DEFAULT (localtime)
timezone = 'UTC'

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.

Actions #7

Updated by Pavel Kácha about 3 years ago

Jan Mach wrote:

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.

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?

Ehh, sorry, copypaste errors, I've entered 11:00:00 and 12:00:00 and they got switched to 13:00:00 and 14:XX:XX.

And yes, I'm not able to reproduce anymore, not sure if it was sun eruptions or now Radko's db conf change.

However, if db default has been changed, it might be necessary to check/verify correct behavior of other timestamps (actionlogs, changelogs, ...).

Actions #8

Updated by Jan Mach about 3 years ago

  • Status changed from Feedback to In Progress
  • Assignee changed from Radko Krkoš to Jan Mach

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.

Actions #9

Updated by Jan Mach about 3 years ago

  • Status changed from In Progress to Feedback
  • Assignee changed from Jan Mach to Pavel Kácha

Everything seems to work.

Actions #10

Updated by Jan Mach about 3 years ago

  • Status changed from Feedback to Closed

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

Actions

Also available in: Atom PDF