Re: BUG #6420: Incorrect description of Postgres time system

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: tom(dot)mcglynn(at)nasa(dot)gov
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6420: Incorrect description of Postgres time system
Date: 2012-01-31 02:21:42
Message-ID: 23016.1327976502@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

tom(dot)mcglynn(at)nasa(dot)gov writes:
> As part of our preparations for the leap second this year I wanted to see
> how Postgres handles this. The only information I could see was
> (Technically, PostgreSQL uses UT1 because
> leap seconds are not handled.)
> in section 9.9 of the manual. This seems to be a misapprehension of what
> the UT1 time system is.

We're just database weenies around here, not astronomers, and I wouldn't
be a bit surprised if we got the terminology wrong.

The long and the short of it is that Postgres totally ignores leap
seconds. We do all calendar arithmetic on the assumption that they
don't exist. It is true that the internal zero reference for timestamp
values is midnight 2000-01-01 in the zero longitude time zone, but it
wouldn't make any visible difference if we'd used some other zero time.

If there's a fancy name for that approach, we'd be pleased to hear what
it is.

> If my inferences from the documentation is correct and Postgres measures the
> number of seconds from UTC 2000-01-01, then the time system used is TAI-32
> seconds. See http://stjarnhimlen.se/comp/time.html for details (and to
> check whether I got the sign right!).

As best I can tell, that document is talking about issues that are
beyond Postgres' ken. When you tell us a timestamp value is
'2012-01-30 21:13:28.097017-05', that's what we store --- whether you
meant it to be in TAI, UTC, UT1, or whatever is not our concern.
If you then ask for that date plus one day, we're going to tell you
'2012-01-31 21:13:28.097017-05'. We do know about civil time zones and
daylight savings transitions, and will adjust such answers for those,
but not about leap seconds. There's been very little user demand for
leap-second-aware date arithmetic, and the difficulties with
extrapolating such arithmetic into the future mean that we're not
likely ever to try to support it.

So anyway, if you want to propose some documentation corrections,
we're all ears.

regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2012-01-31 03:00:36 Re: BUG #6200: standby bad memory allocations on SELECT
Previous Message Tom Lane 2012-01-31 02:03:12 Re: BUG #6421: Revoke column level privilage