https://homeproj.cesnet.cz/https://homeproj.cesnet.cz/httpauth-login/favicon.ico?16194486082023-03-21T09:34:25ZHomeproj: Redmine for CESNETMentat - Bug #7631: Timestamp bounds in event search work badly with timezoneshttps://homeproj.cesnet.cz/issues/7631?journal_id=365152023-03-21T09:34:25ZRajmund Hruška
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>In Progress</i></li></ul><p>I think I know where is the issue here.</p>
<p>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:</p>
<pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">AS</span> <span class="nv">"_mentatq(148_wnbqzt)_"</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-03-07T02:59:55+00:00'</span><span class="p">::</span><span class="n">timestamptz</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><=</span> <span class="s1">'2023-03-07T03:00:00+00:00'</span><span class="p">::</span><span class="n">timestamptz</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="nv">"detecttime"</span> <span class="k">DESC</span> <span class="k">LIMIT</span> <span class="mi">100</span>
</code></pre>
<p>and that's when I realised that the issue is in this query. If the query has <code>timestamp</code> instead of <code>timestamptz</code> or <code>2023-03-07T02:59:55+01:00</code> instead of <code>2023-03-07T02:59:55+00:00</code>, we get the correct result. The timezone in Postgresql is set to localtime.</p>
<p>I don't know how to change the query yet.</p> Mentat - Bug #7631: Timestamp bounds in event search work badly with timezoneshttps://homeproj.cesnet.cz/issues/7631?journal_id=365162023-03-21T09:40:59ZRajmund Hruška
<ul></ul><p>The other option is to set the timezone in Postgresql to UTC, but I am not sure of the consequences.</p> Mentat - Bug #7631: Timestamp bounds in event search work badly with timezoneshttps://homeproj.cesnet.cz/issues/7631?journal_id=365182023-03-21T09:44:01ZRajmund Hruška
<ul><li><strong>Assignee</strong> set to <i>Rajmund Hruška</i></li></ul> Mentat - Bug #7631: Timestamp bounds in event search work badly with timezoneshttps://homeproj.cesnet.cz/issues/7631?journal_id=365252023-03-27T11:20:51ZRajmund Hruška
<ul><li><strong>Status</strong> changed from <i>In Progress</i> to <i>Resolved</i></li><li><strong>Target version</strong> changed from <i>Backlog</i> to <i>2.11</i></li><li><strong>% Done</strong> changed from <i>0</i> to <i>100</i></li></ul><p>It should be fixed in <a class="changeset" title="Fix: Remove timezone information from query. (Redmine issue: #7631)" href="https://homeproj.cesnet.cz/projects/mentat/repository/mentat-ng/revisions/94e0e44f5c3629686988ab88f7205dfbb890960f">94e0e44f</a>.</p> Mentat - Bug #7631: Timestamp bounds in event search work badly with timezoneshttps://homeproj.cesnet.cz/issues/7631?journal_id=365332023-03-30T12:38:47ZRajmund Hruška
<ul><li><strong>Status</strong> changed from <i>Resolved</i> to <i>In Review</i></li></ul><p>Merged into <strong>devel</strong> and deployed on <strong>mentat-alt</strong>.</p> Mentat - Bug #7631: Timestamp bounds in event search work badly with timezoneshttps://homeproj.cesnet.cz/issues/7631?journal_id=365832023-04-14T10:34:29ZRajmund Hruška
<ul><li><strong>Status</strong> changed from <i>In Review</i> to <i>Closed</i></li></ul> Mentat - Bug #7631: Timestamp bounds in event search work badly with timezoneshttps://homeproj.cesnet.cz/issues/7631?journal_id=366102023-04-18T07:13:22ZRajmund Hruška
<ul><li><strong>Status</strong> changed from <i>Closed</i> to <i>In Progress</i></li><li><strong>% Done</strong> changed from <i>100</i> to <i>50</i></li></ul><p>The issue is still present when using quicksearch for last X hours.</p> Mentat - Bug #7631: Timestamp bounds in event search work badly with timezoneshttps://homeproj.cesnet.cz/issues/7631?journal_id=366222023-04-24T10:36:17ZRajmund Hruška
<ul><li><strong>Status</strong> changed from <i>In Progress</i> to <i>Closed</i></li><li><strong>% Done</strong> changed from <i>50</i> to <i>100</i></li></ul>