Time-Zone issue

[mapl]

I started to track my location at 16:33 (UTC+2) and synced my location.

However on the ulogger Server Map, the synced track is not respecting the actual time-zone and adds +2h as shown on the screenshot.

Is there a way to adjust the time-zone setting?

[bfabiszewski]

Setting time zone shouldn't be necessary. Android app sends UTC time of location fix as unix timestamp. It is stored in database as timestamp. When track is displayed the timestamp is converted to browser time zone. So something strange happens in your workflow. It seems the timestamp in your database is 16:34:21 UTC. Maybe the problem is that the time zone in your Android device is incorrectly set as UTC instead of your actual zone: UTC+2? It seems that Android [Location](https://developer.android.com/reference/android/location/Location#getTime()) uses system time rather than GPS time.

[mapl]

It seems that my android has correctly collected the timestamps in UTC as expected.

But what I don't understand. How comes that 2h were added to the UTC timestamp in the tag on the server side?

What could be the issue here? Shouldn't the UTC timestamp be immutable?

Thank you for your input.

---

gpx exported from ulogger server gui after sync

<?xml version="1.0" encoding="UTF-8"?>
<gpx xmlns="http://www.topografix.com/GPX/1/1" xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd https://github.com/bfabiszewski/ulogger-android/1 https://raw.githubusercontent.com/bfabiszewski/ulogger-server/master/scripts/gpx_extensions1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ulogger="https://github.com/bfabiszewski/ulogger-android/1" creator="μlogger-server 1.0-beta" version="1.1">

<trkpt lat="XX.XXXXXXX" lon="X.XXXXXXXXX">
    <ele>95</ele>
    <time>2020-06-03T16:34:21Z</time>
    <name>1</name>
    <extensions>
     <ulogger:speed>19.309999465942</ulogger:speed>
     <ulogger:bearing>147.10000610352</ulogger:bearing>
     <ulogger:accuracy>44</ulogger:accuracy>
     <ulogger:provider>gps</ulogger:provider>
    </extensions>
   </trkpt>

---

gpx exported from android ulogger client

<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>
<gpx xmlns="http://www.topografix.com/GPX/1/1" xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd https://github.com/bfabiszewski/ulogger-android/1 https://raw.githubusercontent.com/bfabiszewski/ulogger-server/master/scripts/gpx_extensions1.xsd" version="1.1" creator="μlogger 2.8" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ulogger="https://github.com/bfabiszewski/ulogger-android/1">

<trkpt lat="XX.XXXXXXX" lon="X.XXXXXXXXX">
        <ele>95.0</ele>
        <time>2020-06-03T14:34:21Z</time>
        <name>1</name>
        <extensions>
          <ulogger:accuracy>43.9519996643066</ulogger:accuracy>
          <ulogger:speed>19.3099994659424</ulogger:speed>
          <ulogger:bearing>147.100006103516</ulogger:bearing>
          <ulogger:provider>gps</ulogger:provider>
        </extensions>
</trkpt>

[bfabiszewski]

That is interesting. Maybe there is some bug in storing/retrieving time from database, specific to your setup. Which database engine do you use? Can you check with direct query what timestamp is really stored in database positions table?

[bfabiszewski]

I found a potential problem that affects PostgreSQL. Is it the engine you use?

[mapl]

Yes, Postgres Is my DB Engine

[bfabiszewski]

It seems there is some inconsistency in behavior between PostgreSQL versions. Could you check if following patch fixes the issue?

diff --git a/helpers/db.php b/helpers/db.php
index 2547087..49105e0 100644
--- a/helpers/db.php
+++ b/helpers/db.php
@@ -159,7 +159,7 @@ require_once(ROOT_DIR . "/helpers/utils.php");
           return "UNIX_TIMESTAMP($column)";
           break;
         case "pgsql":
-          return "EXTRACT(EPOCH FROM $column)";
+          return "EXTRACT(EPOCH FROM $column::TIMESTAMP WITH TIME ZONE)";
           break;
         case "sqlite":
           return "STRFTIME('%s', $column)";

[mapl]

This fixes it :) Nice catch.

gpx export from ulogger server gui

<time>2020-06-03T14:34:21Z</time>

https://stackoverflow.com/questions/29536542/different-results-for-extract-epoch-on-different-postgresql-servers

Yes, the behavior of extract changed in PostgreSQL version 9.2. From the release notes:

Make EXTRACT(EPOCH FROM timestamp without time zone) measure the epoch from local midnight, not UTC midnight (Tom Lane)

This change reverts an ill-considered change made in release 7.3. Measuring from UTC midnight was inconsistent because it made the result dependent on the timezone setting, which computations for timestamp without time zone should not be. The previous behavior remains available by casting the input value to timestamp with time zone.

This might be what is causing the difference, because according to the docs,

The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior.

As @unique_id suggests, using timestamp with time zone (a.k.a. timestamptz) should remove the inconsistency.

[bfabiszewski]

Great! Thanks for help!