1

I want to add a "timestamp with time zone" prior to 1892 in a PostgreSQL database but when I try it I get a timestamp like "1892-05-01 11:59:59+00:17:30" which is not a regular time zone.

I've tried some values and it seems the problem happens for all values prior to 11:42 the 1892/05/01 (UTC+0), and none after.

SELECT '1892-05-03T03:00:00+01:00'::timestamp with time zone;

This gives me 1892-05-03 02:00:00+00 which is expected.

SELECT '1892-04-15T03:00:00+01:00'::timestamp with time zone;

This gives me 1892-04-15 02:17:30+00:17:30 which is wrong.

I've read that it is probably due to time zone handling by PostgreSQL, but is it possible to work around it without deleting the time zone argument of the column?

Yvonnig
  • 69
  • 7

2 Answers2

1

1892-04-15 02:17:30+00:17:30 is not wrong. All of these timestamptz literals represent the exact same point in time:

1892-05-03 03:00:00+01:00
1892-05-03 02:17:30+00:17:30
1892-05-03 02:00:00+00

See:

The root of the confusion is that time was not well standardized until the end of the 19th century. Regions or even cities had their own time zones. Before that, local mean time was "state of the art". And mostly just sun dials before that. Around the end of the 19th century one European country / region after the other started standardizing time. Example: Wikipedia about the history of CET in central Europe.

The Postgres manual about time zones:

Time zones, and time-zone conventions, are influenced by political decisions, not just earth geometry. Time zones around the world became somewhat standardized during the 1900s, but continue to be prone to arbitrary changes, particularly with respect to daylight-savings rules. PostgreSQL uses the widely-used IANA (Olson) time zone database for information about historical time zone rules. [...]

Apparently, there was a change for your undisclosed time zone around 1892-05-01 11:42.

To get to the bottom of this, you need to take into account:

  • The time zone setting of your current session.

    • If it's localtime look at the local time setting of your client OS. Find out with date +%Z in a Unix shell (on your client!).

  • The client you are using (different clients have different ideas how to represent timestamps)

  • Your version of Postgres. The IANA tz database is continuously updated to fix historic errors and add new changes. Currently (Postgres 11.1) uses, according to the readme in the source code:

    The code in this directory is currently synced with tzcode release 2018g. There are many cosmetic (and not so cosmetic) differences from the original tzcode library, but diffs in the upstream version should usually be propagated to our version. Here are some notes about that.

You may depend on the point-release of Postgres (9.5.?), since updates are shipped with point releases, too.

Maybe reconsider your actual requirements. Is there even a problem?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for the detailed answer. If `1892-05-03 03:00:00+01:00` and `1892-05-03 02:17:30+00:17:30` are actually the same, you're right, there is no problem. My issue is that I don't see how they can be the same without a time skip of 25 minutes for my timezone (Europe/Brussels) somewhere. – Yvonnig Jan 04 '19 at 09:37
0

I made the select and returns the expected result. See below:

postgres=# SELECT '1892-04-15T03:00:00+01:00'::timestamp with time zone;

timestamptz

1892-04-15 02:00:00+00 (1 row)

I used the latest version, what is your version of PostgreSql?

Jhonatan Serafim
  • 58
  • 1
  • 5
  • I get the same result with a 9.5 installation on debian stable. I suspect an old timezone settings file or some such. Things get a bit vague before 1900 because timezones weren't widespread then, so it could be a recent adjustment/correction. – Richard Huxton Jan 03 '19 at 20:20
  • Using a 9.5 installation on Windows 10, I get `1892-04-15 02:17:30+00:17:30` as a timestamptz, but I find it strange that it would be different on different version of PostgreSQL. – Yvonnig Jan 04 '19 at 09:26