diff options
| -rw-r--r-- | fietsboek/alembic/versions/20220706_c89d9bdbfa68.py | 36 | ||||
| -rw-r--r-- | fietsboek/models/track.py | 124 | ||||
| -rw-r--r-- | fietsboek/templates/edit.jinja2 | 2 | ||||
| -rw-r--r-- | fietsboek/templates/edit_form.jinja2 | 3 | ||||
| -rw-r--r-- | fietsboek/templates/finish_upload.jinja2 | 2 | ||||
| -rw-r--r-- | fietsboek/util.py | 104 | ||||
| -rw-r--r-- | fietsboek/views/edit.py | 4 | ||||
| -rw-r--r-- | fietsboek/views/upload.py | 13 |
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 |