0

So here I'm working with gpx files. Take note of an excerpt of one:

<?xml version="1.0" encoding="UTF-8"?>
<!-- GPSTrack 2.2.1 — http://bafford.com/gpstrack -->
<gpx xmlns="http://www.topografix.com/GPX/1/1">
<trk>
<name><![CDATA[2016-03-31 10-17-54]]></name>
<trkseg>
<trkpt lat="38.704859" lon="-8.970304"><ele>13.050667</ele><time>2016-03-31T09:17:51Z</time><!-- hAcc=95.768176 vAcc=10.000000 --></trkpt>
<trkpt lat="38.704894" lon="-8.970324"><ele>13.050667</ele><time>2016-03-31T09:17:55Z</time><!-- hAcc=141.087476 vAcc=10.000000 --></trkpt>
<trkpt lat="38.704859" lon="-8.970304"><ele>13.050667</ele><time>2016-03-31T09:17:55Z</time><!-- hAcc=95.768176 vAcc=10.000000 --></trkpt>
<trkpt lat="38.704878" lon="-8.970343"><ele>13.150488</ele><time>2016-03-31T09:18:43Z</time><!-- hAcc=165.000000 vAcc=10.000000 --></trkpt>

See the name of the file? It gives us the time 10H17. Now check the time for each point. It's counted with one less hour.

I still don't get how the times are messed up here. But this is the beginning of the problem.

Now, I'm parsing these many gpx files, and loading them into a PostgreSQL database. More specifically this table:

CREATE TABLE IF NOT EXISTS trips (
  trip_id SERIAL PRIMARY KEY,

  start_location TEXT REFERENCES locations(label),
  end_location TEXT REFERENCES locations(label),

  start_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  end_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,

  bounds geography(POLYGONZ, 4326) NOT NULL,
  points geography(LINESTRINGZ, 4326) NOT NULL,

  timestamps TIMESTAMP WITHOUT TIME ZONE[] NULL
);

Even while using TIMESTAMP WITHOUT TIME ZONE the points are being loaded with the wrong hour (one less hour). This happens only in the days after the Daylight Savings Time is in effect. The point here: is there any way to check if a date is on DST time and add one hour to it if that checks?

I checked for tm_isdst and datetime.dst() but I still don't understand it.

Shoplifter.Doe
  • 118
  • 4
  • 16

1 Answers1

0

If you know the time zone name where this local time is supposed to be set in (assuming 'Europe/Vienna' in the example), you can normalize the value to correct UTC time (or any other time zone) with this expression:

SELECT '2016-03-31T09:17:51Z'::timestamp AT TIME ZONE 'Europe/Vienna' AT TIME ZONE 'UTC'

Result:

timezone
--------------------
2016-03-31 07:17:51

Yes, apply AT ZIME ZONE twice.

Since you seem to be dealing with different time zones I would consider using timestamptz instead of timestamp in your tables.

Be sure to use time zone names, not abbreviations or plain time offsets to get precise adjustments for DST.
I hate the moronic concept of "daylight saving time" - it never saves any daylight but keeps wasting valuable time of people being confused by it.

Detailed explanation for all of that:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • well, although I agree with you on DST wasting us time, it sure is nice to have one more hour of sun during the summer haha. So, regarding my problem, I understood your answer, and I changed my table to have `TIMESTAMP WITH TIME ZONE`. Although the date is collected in the same country, this DST bore is taken as a timezone. Using the `pgAdmin` tool I now see that my table shows the date like this `2016-03-31 09:17:51+01`. This means that when I query for the date and pass it to whatever function I want, it will consider it as 10:17, right? – Shoplifter.Doe Aug 01 '16 at 15:29
  • @Shoplifter.Doe: [As explained in the linked answer](http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170), the *display* of `timestamptz` is adapted to the time zone setting of the session. You can always use the `AT TIME ZONE` construct once more to get the display for a given time zone. When you *cast* to `date`, the current time zone setting is taken into consideration as well. – Erwin Brandstetter Aug 01 '16 at 16:40
  • Yeah I got it. The time zone setting of the session is more than enough. "Daylight Saving Time is not among the brightest ideas humanity ever came up with." - for programming issues this is the case indeed. For me the `SELECT start_date::timestamp AT TIME ZONE 'UTC' from trips` expression is the one that works for me. Much like your "loaded footgun" from the linked answer. In my front-end I can deal with these dates easily with some JS libraries. I was far from knowing that these timezones can be a pain. Thank you for the lesson. – Shoplifter.Doe Aug 01 '16 at 21:14