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

BUG #6420: Incorrect description of Postgres time system

From
tom.mcglynn@nasa.gov
Date:
The following bug has been logged on the website:

Bug reference:      6420
Logged by:          Thomas McGlynn
Email address:      tom.mcglynn@nasa.gov
PostgreSQL version: 9.1.2
Operating system:   Any
Description:=20=20=20=20=20=20=20=20

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=20
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.  UT1 measures mean solar time -- days are not
exactly 86400 seconds long.  Currently UT1 and UTC never differ by more than
one second.  Leap seconds are the way this correspondence is kept. What I
believe you should be saying is that you use TAI -- atomic time -- with some
offset.

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!).

I think this should be clearly stated in the documentation when discussing
the time types but I did not see it.

Regards...

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

From
Tom Lane
Date:
tom.mcglynn@nasa.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