From 2cf5859a0bdb8771a73cd8fd65a45da5503cf184 Mon Sep 17 00:00:00 2001
From: Daniel Schadt <kingdread@gmx.de>
Date: Wed, 6 Jul 2022 15:37:01 +0200
Subject: better timezone handling for track dates
---
.../alembic/versions/20220706_c89d9bdbfa68.py | 36 ++++++
fietsboek/models/track.py | 124 +++++++++++++++++++--
fietsboek/templates/edit.jinja2 | 2 +-
fietsboek/templates/edit_form.jinja2 | 3 +-
fietsboek/templates/finish_upload.jinja2 | 2 +-
fietsboek/util.py | 104 ++++++++++++++++-
fietsboek/views/edit.py | 4 +
fietsboek/views/upload.py | 13 ++-
8 files changed, 272 insertions(+), 16 deletions(-)
create mode 100644 fietsboek/alembic/versions/20220706_c89d9bdbfa68.py
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
--
cgit v1.2.3