better timezone handling for track dates

8 files changed, 272 insertions, 16 deletions

diff --git a/fietsboek/alembic/versions/20220706_c89d9bdbfa68.py b/fietsboek/alembic/versions/20220706_c89d9bdbfa68.py
new file mode 100644
index 0000000..83b2978
--- /dev/null
+++ b/fietsboek/alembic/versions/20220706_c89d9bdbfa68.py
@@ -0,0 +1,36 @@
+"""Add timezone information to tracks
+
+Revision ID: c89d9bdbfa68
+Revises: 1b4b1c179e5a
+Create Date: 2022-07-06 14:05:15.431716
+
+"""
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision = 'c89d9bdbfa68'
+down_revision = '1b4b1c179e5a'
+branch_labels = None
+depends_on = None
+
+def upgrade():
+    op.alter_column('track_cache', 'start_time', new_column_name='start_time_raw')
+    op.alter_column('track_cache', 'end_time', new_column_name='end_time_raw')
+    op.add_column('track_cache', sa.Column('start_time_tz', sa.Integer(), nullable=True))
+    op.add_column('track_cache', sa.Column('end_time_tz', sa.Integer(), nullable=True))
+    op.alter_column('tracks', 'date', new_column_name='date_raw')
+    op.add_column('tracks', sa.Column('date_tz', sa.Integer(), nullable=True))
+
+    op.execute('UPDATE tracks SET date_tz=0;')
+    op.execute('UPDATE track_cache SET start_time_tz=0, end_time_tz=0;')
+
+
+def downgrade():
+    op.alter_column('tracks', 'date_raw', new_column_name='date')
+    op.drop_column('tracks', 'date_tz')
+    op.alter_column('track_cache', 'start_time_raw', new_column_name='start_time')
+    op.alter_column('track_cache', 'end_time_raw', new_column_name='end_time')
+    op.drop_column('track_cache', 'start_time_tz')
+    op.drop_column('track_cache', 'end_time_tz')
diff --git a/fietsboek/models/track.py b/fietsboek/models/track.py
index 1b297fe..58e9745 100644
--- a/fietsboek/models/track.py
+++ b/fietsboek/models/track.py
@@ -14,6 +14,7 @@ meta information.
 import enum
 import gzip
 import datetime
+import logging
 
 from itertools import chain
 
@@ -44,6 +45,9 @@ from .meta import Base
 from .. import util
 
 
+LOGGER = logging.getLogger(__name__)
+
+
 class Tag(Base):
     """A tag is a single keyword associated with a track.
 
@@ -95,6 +99,27 @@ track_badge_assoc = Table(
     Column("badge_id", ForeignKey("badges.id"), primary_key=True),
 )
 
+# Some words about timezone handling in saved tracks:
+# https://www.youtube.com/watch?v=-5wpm-gesOY
+#
+# We usually want to store the times and dates in UTC, and then convert them
+# later to the user's display timezone. However, this is not quite right for
+# tracks, as the time of tracks is what JSR-310 would call a "LocalDateTime" -
+# that is, a date and time in the calendar without timezone information. This
+# makes sense: Imagine you go on vacation and cycle at 9:00 AM, you don't want
+# that to be displayed as a different time once you return. Similarly, you
+# would tell "We went on a tour at 9 in the morning", not convert that time to
+# the local time of where you are telling the story.
+#
+# We therefore save the dates of tracks as a local time, but still try to guess
+# the UTC offset so that *if needed*, we can get the UTC timestamp. This is
+# done by having two fields, one for the "naive" local datetime and one for the
+# UTC offset in minutes. In most cases, we don't care too much about the
+# offset, as long as the displayed time is alright for the user.
+#
+# The issue then mainly consists of getting the local time from the GPX file,
+# as some of them store timestamps as UTC time. The problem of finding some
+# local timestamp is delegated to util.guess_gpx_timestamp().
 
 class Track(Base):
     """A :class:`Track` represents a single GPX track.
@@ -112,8 +137,10 @@ class Track(Base):
     :vartype title: str
     :ivar description: Textual description of the track.
     :vartype description: str
-    :ivar date: Set date of the track.
-    :vartype date: datetime.datetime
+    :ivar date_raw: Set date of the track (naive :class:`~datetime.datetime`).
+    :vartype date_raw: datetime.datetime
+    :ivar date_tz: Timezone offset of the locale date in minutes.
+    :vartype date_tz: int
     :ivar gpx: Compressed GPX data.
     :vartype gpx: bytes
     :ivar visibility: Visibility of the track.
@@ -138,7 +165,8 @@ class Track(Base):
     owner_id = Column(Integer, ForeignKey('users.id'))
     title = Column(Text)
     description = Column(Text)
-    date = Column(DateTime)
+    date_raw = Column(DateTime)
+    date_tz = Column(Integer)
     gpx = Column(LargeBinary)
     visibility = Column(Enum(Visibility))
     link_secret = Column(Text)
@@ -227,6 +255,32 @@ class Track(Base):
     def gpx_data(self, value):
         self.gpx = gzip.compress(value)
 
+    @property
+    def date(self):
+        """The time-zone-aware date this track has set.
+
+        This combines the :attr:`date_raw` and :attr:`date_tz` values to
+        provide a timezone aware :class:`~datetime.datetime`.
+
+        :return: The aware datetime.
+        :rtype: datetime.datetime
+        """
+        if self.date_tz is None:
+            timezone = datetime.timezone.utc
+        else:
+            timezone = datetime.timezone(datetime.timedelta(minutes=self.date_tz))
+        return self.date_raw.replace(tzinfo=timezone)
+
+    @date.setter
+    def date(self, value):
+        if value.tzinfo is None:
+            LOGGER.debug('Non-aware datetime passed (track_id=%d, value=%s), assuming offset=0',
+                         self.id, value)
+            self.date_tz = 0
+        else:
+            self.date_tz = value.tzinfo.utcoffset(value).total_seconds() // 60
+        self.date_raw = value.replace(tzinfo=None)
+
     def is_visible_to(self, user):
         """Checks whether the track is visible to the given user.
 
@@ -477,10 +531,14 @@ class TrackCache(Base):
     :vartype max_speed: float
     :ivar avg_speed: Average speed, in meters/second.
     :vartype avg_speed: float
-    :ivar start_time: Start time of the GPX recording.
-    :vartype start_time: datetime.datetime
-    :ivar end_time: End time of the GPX recording.
-    :vartype end_time: datetime.datetime
+    :ivar start_time_raw: Start time of the GPX recording.
+    :vartype start_time_raw: datetime.datetime
+    :ivar start_time_tz: Timezone offset of the start time in minutes.
+    :vartype start_time_tz: int
+    :ivar end_time_raw: End time of the GPX recording.
+    :vartype end_time_raw: datetime.datetime
+    :ivar end_time_tz: Timezone offset of the end time in minutes.
+    :vartype end_time_tz: int
     :ivar track: The track that belongs to this cache entry.
     :vartype track: Track
     """
@@ -494,11 +552,59 @@ class TrackCache(Base):
     stopped_time = Column(Float)
     max_speed = Column(Float)
     avg_speed = Column(Float)
-    start_time = Column(DateTime)
-    end_time = Column(DateTime)
+    start_time_raw = Column(DateTime)
+    start_time_tz = Column(Integer)
+    end_time_raw = Column(DateTime)
+    end_time_tz = Column(Integer)
 
     track = relationship('Track', back_populates='cache')
 
+    @property
+    def start_time(self):
+        """The time-zone-aware start time of this track.
+
+        :return: The aware datetime.
+        :rtype: datetime.datetime
+        """
+        if self.start_time_tz is None:
+            timezone = datetime.timezone.utc
+        else:
+            timezone = datetime.timezone(datetime.timedelta(minutes=self.start_time_tz))
+        return self.start_time_raw.replace(tzinfo=timezone)
+
+    @start_time.setter
+    def start_time(self, value):
+        if value.tzinfo is None:
+            LOGGER.debug('Non-aware datetime passed (cache_id=%d, value=%s), assuming offset=0',
+                         self.id, value)
+            self.start_time_tz = 0
+        else:
+            self.start_time_tz = value.tzinfo.utcoffset(value).total_seconds() // 60
+        self.start_time_raw = value.replace(tzinfo=None)
+
+    @property
+    def end_time(self):
+        """The time-zone-aware end time of this track.
+
+        :return: The aware datetime.
+        :rtype: datetime.datetime
+        """
+        if self.end_time_tz is None:
+            timezone = datetime.timezone.utc
+        else:
+            timezone = datetime.timezone(datetime.timedelta(minutes=self.end_time_tz))
+        return self.end_time_raw.replace(tzinfo=timezone)
+
+    @end_time.setter
+    def end_time(self, value):
+        if value.tzinfo is None:
+            LOGGER.debug('Non-aware datetime passed (cache_id=%d, value=%s), assuming offset=0',
+                         self.id, value)
+            self.end_time_tz = 0
+        else:
+            self.end_time_tz = value.tzinfo.utcoffset(value).total_seconds() // 60
+        self.end_time_raw = value.replace(tzinfo=None)
+
 
 class Upload(Base):
     """A track that is currently being uploaded.
diff --git a/fietsboek/templates/edit.jinja2 b/fietsboek/templates/edit.jinja2
index fd26f6b..9d48cf1 100644
--- a/fietsboek/templates/edit.jinja2
+++ b/fietsboek/templates/edit.jinja2
@@ -9,7 +9,7 @@
     <noscript><p>{{ _("page.noscript") }}<p></noscript>
   </div>
   <form method="POST">
-    {{ edit_form.edit_track(track.title, track.date, track.visibility, track.description, track.text_tags(), badges, track.tagged_people) }}
+    {{ edit_form.edit_track(track.title, track.date_raw, track.date_tz or 0, track.visibility, track.description, track.text_tags(), badges, track.tagged_people) }}
     <div class="btn-group" role="group">
         <button type="submit" class="btn btn-primary"><i class="bi bi-save"></i> {{ _("page.edit.form.submit") }}</button>
         <a href="{{ request.route_url('details', track_id=track.id) }}" class="btn btn-secondary"><i class="bi bi-x-circle"></i> {{ _("page.edit.form.cancel") }}</a>
diff --git a/fietsboek/templates/edit_form.jinja2 b/fietsboek/templates/edit_form.jinja2
index 6078264..b43e5d5 100644
--- a/fietsboek/templates/edit_form.jinja2
+++ b/fietsboek/templates/edit_form.jinja2
@@ -1,4 +1,4 @@
-{% macro edit_track(title, date, visibility, description, tags, badges, friends) %}
+{% macro edit_track(title, date, date_tz, visibility, description, tags, badges, friends) %}
 <div class="mb-3">
   <label for="formTitle" class="form-label">{{ _("page.track.form.title") }}</label>
   <input class="form-control" type="text" id="formTitle" name="title" value="{{ title | default("", true) }}">
@@ -6,6 +6,7 @@
 <div class="mb-3">
   <label for="formDate" class="form-label">{{ _("page.track.form.date") }}</label>
   <input class="form-control" type="datetime-local" id="formDate" name="date" value="{{ date.strftime('%Y-%m-%dT%H:%M') }}">
+  <input type="hidden" name="date-tz" value="{{ date_tz }}">
 </div>
 <div class="mb-3">
   <label for="formVisibility" class="form-label">{{ _("page.track.form.visibility") }}</label>
diff --git a/fietsboek/templates/finish_upload.jinja2 b/fietsboek/templates/finish_upload.jinja2
index a737464..66ba926 100644
--- a/fietsboek/templates/finish_upload.jinja2
+++ b/fietsboek/templates/finish_upload.jinja2
@@ -9,7 +9,7 @@
     <noscript><p>{{ _("page.noscript") }}<p></noscript>
   </div>
   <form method="POST">
-    {{ edit_form.edit_track(upload_title, upload_date, upload_visibility, upload_description, upload_tags, badges, upload_tagged_people) }}
+    {{ edit_form.edit_track(upload_title, upload_date, upload_date_tz, upload_visibility, upload_description, upload_tags, badges, upload_tagged_people) }}
     <div class="btn-group" role="group">
       <button type="submit" class="btn btn-primary">{{ _("page.upload.form.submit") }}</button>
       <a href="{{ request.route_url('cancel-upload', upload_id=preview_id) }}" class="btn btn-danger">{{ _("page.upload.form.cancel") }}</a>
diff --git a/fietsboek/util.py b/fietsboek/util.py
index 56f0656..368cfcd 100644
--- a/fietsboek/util.py
+++ b/fietsboek/util.py
@@ -1,6 +1,7 @@
 """Various utility functions."""
 import random
 import string
+import datetime
 
 import babel
 import markdown
@@ -38,6 +39,104 @@ def safe_markdown(md_source):
     return Markup(html)
 
 
+def fix_iso_timestamp(timestamp):
+    """Fixes an ISO timestamp to make it parseable by
+    :meth:`datetime.datetime.fromisoformat`.
+
+    This removes an 'Z' if it exists at the end of the timestamp, and replaces
+    it with '+00:00'.
+
+    :param timestamp: The timestamp to fix.
+    :type timestamp: str
+    :return: The fixed timestamp.
+    :rtype: str
+    """
+    if timestamp.endswith('Z'):
+        return timestamp[:-1] + '+00:00'
+    return timestamp
+
+
+def round_timedelta_to_multiple(value, multiples):
+    """Round the timedelta `value` to be a multiple of `multiples`.
+
+    :param value: The value to be rounded.
+    :type value: datetime.timedelta
+    :param multiples: The size of each multiple.
+    :type multiples: datetime.timedelta
+    :return: The rounded value.
+    :rtype: datetime.timedelta
+    """
+    lower = value.total_seconds() // multiples.total_seconds() * multiples.total_seconds()
+    second_offset = value.total_seconds() - lower
+    if second_offset < multiples.total_seconds() // 2:
+        # Round down
+        return datetime.timedelta(seconds=lower)
+    # Round up
+    return datetime.timedelta(seconds=lower) + multiples
+
+
+def guess_gpx_timezone(gpx):
+    """Guess which timezone the GPX file was recorded in.
+
+    This looks at a few timestamps to see if they have timezone information
+    attached, including some known GPX extensions.
+
+    :param gpx: The parsed GPX file to analyse.
+    :type gpx: gpxpy.GPX
+    :return: The timezone information.
+    :rtype: datetime.timezone
+    """
+    time_bounds = gpx.get_time_bounds()
+    times = [
+        gpx.time,
+        time_bounds.start_time,
+        time_bounds.end_time,
+    ]
+    times = [time for time in times if time]
+    # First, we check if any of the timestamps has a timezone attached. Note
+    # that some devices save their times in UTC, so we need to look for a
+    # timestamp different than UTC.
+    for time in times:
+        if  time.tzinfo and time.tzinfo.utcoffset(time):
+            return time.tzinfo
+
+    # Next, we look if there's a "localTime" extension on the track, so we can
+    # compare the local time to the time.
+    for track in gpx.tracks:
+        time = times[0]
+        local_time = None
+        for extension in track.extensions:
+            if extension.tag.lower() == 'localtime':
+                local_time = datetime.datetime.fromisoformat(
+                    fix_iso_timestamp(extension.text)).replace(tzinfo=None)
+            elif extension.tag.lower() == 'time':
+                time = datetime.datetime.fromisoformat(
+                    fix_iso_timestamp(extension.text)).replace(tzinfo=None)
+        if time is not None and local_time is not None:
+            # We found a pair that we can use!
+            offset = local_time - time
+            # With all the time madness, luckily most time zones seem to stick
+            # to an offset that is a multiple of 15 minutes (see
+            # https://en.wikipedia.org/wiki/List_of_UTC_offsets). We try to
+            # round the value to the nearest of 15 minutes, to prevent any
+            # funky offsets from happening due to slight clock desyncs.
+            offset = round_timedelta_to_multiple(offset, datetime.timedelta(minutes=15))
+            return datetime.timezone(offset)
+
+    # Special case for MyTourbook exports, which have a 'mt:TourStartTime' extension
+    if gpx.time:
+        for extension in gpx.metadata_extensions:
+            if extension.tag.lower() == '{net.tourbook/1}tourstarttime':
+                local_time = datetime.datetime.fromtimestamp(int(extension.text) // 1000)
+                time = gpx.time.astimezone(datetime.timezone.utc).replace(tzinfo=None)
+                offset = local_time - time
+                offset = round_timedelta_to_multiple(offset, datetime.timedelta(minutes=15))
+                return datetime.timezone(offset)
+
+    # If all else fails, we assume that we are UTC+00:00
+    return datetime.timezone.utc
+
+
 def tour_metadata(gpx_data):
     """Calculate the metadata of the tour.
 
@@ -51,6 +150,7 @@ def tour_metadata(gpx_data):
     :rtype: dict
     """
     gpx = gpxpy.parse(gpx_data)
+    timezone = guess_gpx_timezone(gpx)
     uphill, downhill = gpx.get_uphill_downhill()
     moving_data = gpx.get_moving_data()
     time_bounds = gpx.get_time_bounds()
@@ -62,8 +162,8 @@ def tour_metadata(gpx_data):
         'stopped_time': moving_data.stopped_time,
         'max_speed': moving_data.max_speed,
         'avg_speed': moving_data.moving_distance / moving_data.moving_time,
-        'start_time': time_bounds.start_time,
-        'end_time': time_bounds.end_time,
+        'start_time': time_bounds.start_time.astimezone(timezone),
+        'end_time': time_bounds.end_time.astimezone(timezone),
     }
 
 
diff --git a/fietsboek/views/edit.py b/fietsboek/views/edit.py
index 5d91ea8..91bac60 100644
--- a/fietsboek/views/edit.py
+++ b/fietsboek/views/edit.py
@@ -49,6 +49,10 @@ def do_edit(request):
     if any(user not in track.tagged_people and user not in user_friends for user in tagged_people):
         return HTTPBadRequest()
 
+    tz_offset = datetime.timedelta(minutes=int(request.params["date-tz"]))
+    date = datetime.datetime.fromisoformat(request.params["date"])
+    track.date = date.replace(tzinfo=datetime.timezone(tz_offset))
+
     track.tagged_people = tagged_people
     track.title = request.params["title"]
     track.visibility = Visibility[request.params["visibility"]]
diff --git a/fietsboek/views/upload.py b/fietsboek/views/upload.py
index 5e67aa5..4ce41c8 100644
--- a/fietsboek/views/upload.py
+++ b/fietsboek/views/upload.py
@@ -103,11 +103,16 @@ def finish_upload(request):
     badges = request.dbsession.execute(select(models.Badge)).scalars()
     badges = [(False, badge) for badge in badges]
     gpx = gpxpy.parse(upload.gpx_data)
+    timezone = util.guess_gpx_timezone(gpx)
+    date = gpx.time or gpx.get_time_bounds().start_time
+    date = date.astimezone(timezone)
+    tz_offset = timezone.utcoffset(date)
 
     return {
         'preview_id': upload.id,
         'upload_title': gpx.name,
-        'upload_date': gpx.time or datetime.datetime.now(),
+        'upload_date': date,
+        'upload_date_tz': int(tz_offset.total_seconds() // 60),
         'upload_visibility': Visibility.PRIVATE,
         'upload_description': gpx.description,
         'upload_tags': set(),
@@ -134,16 +139,20 @@ def do_finish_upload(request):
     if any(user not in user_friends for user in tagged_people):
         return HTTPBadRequest()
 
+    tz_offset = datetime.timedelta(minutes=int(request.params["date-tz"]))
+    date = datetime.datetime.fromisoformat(request.params["date"])
+    date = date.replace(tzinfo=datetime.timezone(tz_offset))
+
     track = models.Track(
         owner=request.identity,
         title=request.params["title"],
-        date=datetime.datetime.fromisoformat(request.params["date"]),
         visibility = Visibility[request.params["visibility"]],
         description=request.params["description"],
         badges=badges,
         link_secret=util.random_alphanum_string(),
         tagged_people=tagged_people,
     )
+    track.date = date
     tags = request.params.getall("tag[]")
     track.sync_tags(tags)
     track.gpx_data = upload.gpx_data