Page MenuHomeSoftware Heritage

grab_next_visits: avoid time interval calculations in PostgreSQL
ClosedPublic

Authored by olasd on Oct 27 2021, 3:49 PM.

Details

Summary

When the database is in a non-UTC timezone with DST, and a `timestamptz

  • interval` calculation crosses a DST change, the result of the

calculation can be one hour off from the expected value:

PostgreSQL will vary the timestamp by the amount of days in the
interval, and will keep the same (local) time, which will be offset by
an hour because of the DST change.

Doing the datetime +- timedelta calculations in Python instead of
PostgreSQL avoids this caveat altogether.

Test Plan

tox passes when the database is in Europe/Paris timezone

Diff Detail

Repository
rDSCH Scheduling utilities
Branch
master
Lint
No Linters Available
Unit
No Unit Test Coverage
Build Status
Buildable 24731
Build 38604: Phabricator diff pipeline on jenkinsJenkins console · Jenkins
Build 38603: arc lint + arc unit

Event Timeline

Build is green

Patch application report for D6563 (id=23848)

Rebasing onto ecc0e2803e...

Current branch diff-target is up to date.
Changes applied before test
commit 0c7ef27b7e0542d2795a9720ebb5af2a3b274e18
Author: Nicolas Dandrimont <nicolas@dandrimont.eu>
Date:   Wed Oct 27 15:45:09 2021 +0200

    grab_next_visits: avoid time interval calculations in PostgreSQL
    
    When the database is in a non-UTC timezone with DST, and a `timestamptz
    - interval` calculation crosses a DST change, the result of the
    calculation can be one hour off from the expected value:
    
    PostgreSQL will vary the timestamp by the amount of days in the
    interval, and will keep the same (local) time, which will be offset by
    an hour because of the DST change.
    
    Doing the datetime +- timedelta calculations in Python instead of
    PostgreSQL avoids this caveat altogether.

See https://jenkins.softwareheritage.org/job/DSCH/job/tests-on-diff/482/ for more details.

olasd requested review of this revision.Oct 27 2021, 3:52 PM
ardumont added a subscriber: ardumont.

ah, now i got the laugh on irc ;)

This revision is now accepted and ready to land.Oct 27 2021, 3:57 PM

A bit sad, but hey, thanks!