Search code examples
postgresqltimestamptimezone-offsettimestamp-with-timezonepostgresql-9.6

Strange time zone in PostgreSQL timestamp conversion


This SQL:

select to_timestamp(extract(epoch from '0001-01-01 00:00:00'::timestamp))

produces this output:

0001-01-01 08:06:00+08:06

I realize that to_timestamp() always adds a time zone, hence the additional 8 hours and +8 in the time zone segment. But what is the :06? And where did the extra 6 minutes come from?

EDIT

If I initially execute set local timezone to 'UTC'; then I get expected results.


Solution

  • Before UTC was invented, each city had its own local time, mostly with a difference of just some minutes among each other.

    Just after standardization of timezones (and the respective adoption by everybody), the local times were set to the values we know today.

    That's why you get these strange results for ancient dates, specially before the year 1900.

    Actually, Taipei changed from UTC+08:06 to UTC+08:00 only in Jan 1st of 1896, so dates before it will have the +08:06 offset.

    If you set your timezone to UTC this doesn't happen, basically because UTC's offset is zero and never changes.