As a minor note, the SQL standard requires timestamps to be in the
format (simplified to ignore timezones, variable precisions, etc)
yyyymmddhhmmss.ssssss where it is currently for the implementor to
decide how to do the date and time manipulations.
SQL 2008 standard
4.6.2 Datetimes (extracts)
— TIMESTAMP — contains the <primary datetime field>s YEAR, MONTH, DAY,
HOUR, MINUTE, and
SECOND
A datetime value, of data type TIME WITHOUT TIME ZONE or TIMESTAMP
WITHOUT TIME ZONE,
may represent a local time, whereas a datetime value of data type TIME
WITH TIME ZONE or TIMESTAMP
WITH TIME ZONE represents UTC.
in the SQL 2008 standard see NOTE 101 — Datetime data types will allow
dates in the Gregorian format to be stored in the date range 0001–01–01
CE through
9999–12–31 CE. The range for SECOND allows for as many as two “leap
seconds”. Interval arithmetic that involves leap seconds
or discontinuities in calendars will produce implementation-defined results.
Robert
On 26/12/2016 13:43, Brooks Harris wrote:
On 2016-12-25 11:58 PM, John Sauter wrote:
On Sun, 2016-12-25 at 18:53 -0800, Steve Allen wrote:
On Sun 2016-12-25T19:37:31 -0700, Warner Losh hath writ:
I think that POSIX has de-facto redefined UTC, and it's time that
the
UTC standard catch up to this quiet revolution.
POSIX has defined that the time scale upon which time_t is based has
the characteristics of every time scale other than UTC. That is,
there are 86400 "seconds" in a "day". That will stay true until
POSIX is in use for civil time somewhere else than on earth.
The fact that POSIX defines a time scale different from UTC is a
problem for POSIX, not for UTC. People use UTC in their computers, and
don't care what the POSIX standard says.
I understand your meaning here, I think, that the "time" as presented
to users is meant to be UTC, or "based on UTC", and it is, most of the
time, except for the pesky Leap Second introductions and some other
local time difficulties.
But fundamentally, PCs are using POSIX, (or something like it -
Windows behaves the same except uses "FILETIME" as "time_t" with a
1601 epoch) to count seconds and generate YMDhms representations
("broken down time"). Its a pure Gregorian calendar 86400-second-day
counting scheme. Temporary adjustment is made by some systems to
time_t for Leap Seconds such that the YMDhms representation comes out
the way humans expect, aligned to UTC, more or less. As David Mills
point out -
"...Thus, when a leap second is inserted in UTC and subsequently in
NTP or POSIX, knowledge of all previous leap seconds is lost. ..
Another way to describe this is to say there are as many NTP or POSIX
timescales as historic leap seconds. In effect, a new timescale is
reestablished after each new leap second. "
The NTP Timescale and Leap Seconds
3. How NTP and POSIX Reckon with Leap Seconds
https://www.eecis.udel.edu/~mills/leap.html
The time_t 1970 epoch is fixed with respect to internal POSIX
calculations, but it "slips" a second with respect to UTC with each
(positive) Leap Second introduction because "23:59:60" goes missing.
(In discussions we typically honor, or play lip service to, the
possibility of a negative Leap Second, but since one has never
happened you wonder how many parts of the systems might break if IERS
declares one :-\ )
86400-second-day systems are ubiquitous, from operating systems and
the time services they provide, to nearly every application known to
mankind; file systems, email, SQL, banking, airline scheduling, Excel,
Quickbooks, you name it. Not to mention c++, Java, Javascript, .NET,
etc, etc. and nearly every calendar and time library available. That
giant legacy will not be displaced by any new timekeeping system any
time soon. There is no displacing POSIX time and the heritage of the
86400-second-day; its baked into the infrastructure until there is
some major paradigm shift that compels updating all those systems and
applications, and that just isn't going to happen in the foreseeable
future.
Any new timekeeping solution must be reverse compatible to POSIX time
and 86400-second-day systems.
It is less clear is what kind of "seconds" and "days" POSIX wants,
largely because the current UTC did not make the distinction clear.
Thus for POSIX the "seconds" being ticked and "days" of the calendar
must be related by the factor 86400, which is not the case in UTC as
defined by CCIR/ITU-R.
It is clear that devices implementing POSIX must rely on the time
signals in radio broadcasts. Too much infrastructure already exists
which relies on those broadcasts.
It is clear that the agencies implementing the radio broadcasts will
never return to rubber seconds, and will not approve of smears.
So the only change that can happen to the nature of radio broadcasts
is to start counting atomic days of atomic seconds.
No, that isn't the only change that can happen. Another possibility is
that the radio broadcasts do not change and people ignore the POSIX
standard, keeping time according to UTC. As long as you don't use
POSIX time_t, that is doable.
I don't believe the time dissemination services can change any more
than POSIX time, 86400-second-day applications, or UTC itself can be
changed all for the same reason - too many down stream systems rely on
their behavior. USNO, NIST, WWV, DCF77, MSF are not going to change
their procedures; they just can't - everything downstream would break.
Any new timekeeping solution must be reverse compatible to existing
time dissemination services.
Note these services use the common practice of introducing Leap
Seconds on local timescale simultaneous with their occurrence on the
UTC timescale. This creates a discontinuity in the YMDhms count in
midday on most local timescales, further complicated by possible
Daylight Savings. Many systems follow this convention, tangling UTC
with POSIX-time-type calculations and local time definitions and rules
(Tz Database, for example).
Any new timekeeping solution must be reverse compatible to these
existing local time representations.
We're stuck with a giant legacy of 86400-second-day based systems,
from the operating systems to applications that rely on them, from SQL
to banking systems, from Excel to email. These systems must continue
to operate as expected, even if they're all inaccurate with respect to
UTC and/or ambiguous with respect to local time representations.
We're stuck with Leap Seconds for the foreseeable future. (I like it
for its conceptual and academic purity but it obviously causes all
the headaches and frustration for practical timekeeping we are
discussing.)
We're stuck with several "smears" that both "squeezes" UTC into
86400-second-day systems and protects those systems from potential
errors and failures that may occur with Leap Second adjustments. This
at the expensive of altering the frequency of the reference timebase,
which is unacceptable for many purposes. (Imagine the consequences if
GPS smeared its frequency :-| )
We're probably stuck with Microsoft's Azure that adds another approach
to "squeezing" Leap Seconds into the Windows 86400-second-day.
So now there's at least four timekeeping methods that must be considered-
1) UTC itself, which is hard to understand due to its many dispersed
specifications and has no way to automatically obtain the crucial Leap
Seconds metadata, so it may not be consistently implemented.
2) Legacy 86400-second-day systems, which are well known but
inaccurate with respect to UTC and ambiguous with respect to local
time representation.
3) Several smeared systems that are intentionally inaccurate with
respect to both UTC and 86400-second-day systems.
4) Microsoft Azure that uses a unique way to follow UTC while Windows
PCs just catch up sometime.
Meantime local time is said to be "based on UTC" but no clear
specification of local time with respect to UTC exists.
I wonder if the computer industry might not have long since adopted a
more accurate UTC-based local time timekeeping system if a
specification for one actually existed. But it doesn't, and that, it
seems to me, should be the objective; to develop a suite of
specifications that consolidates the UTC specs, codifies the meaning
and representation of local time, and specifies reverse compatible
mapping to existing systems. This needs to be accompanied by format
and protocol specifications together with reference implementations.
Without a plan its hard to imagine how convergence on a uniform
timekeeping system comes about.
The existing systems are not going to be replaced; they all need to be
supported while defining a more uniform and UTC-accurate set of
procedures that may be used in parallel with, beside, on top of, or in
conjunction with, existing systems. If we keep arguing about what UTC
and POSIX are or are not because we don't like one or the other I
don't see how we get very far.
-Brooks
It is not clear that the name UTC is sacrosanct to POSIX.
The machines simply do not care what humans call that time scale.
It is clear that some humans do care about the name UTC. I have not
seen any of the agencies make arguments about why the radio broadcast
time scale cannot change its name. It has done so in the past, and
the world did not care.
--
Steve Allen<s...@ucolick.org> WGS-84
(GPS)
UCO/Lick Observatory--ISB 260 Natural Sciences II, Room
165 Lat +36.99855
1156 High Street Voice: +1 831 459 3046 Lng
-122.06015
Santa Cruz, CA 95064http://www.ucolick.org/~sla/ Hgt
+250 m
_______________________________________________
LEAPSECS mailing list
LEAPSECS@leapsecond.com
https://pairlist6.pair.net/mailman/listinfo/leapsecs
_______________________________________________
LEAPSECS mailing list
LEAPSECS@leapsecond.com
https://pairlist6.pair.net/mailman/listinfo/leapsecs
_______________________________________________
LEAPSECS mailing list
LEAPSECS@leapsecond.com
https://pairlist6.pair.net/mailman/listinfo/leapsecs
_______________________________________________
LEAPSECS mailing list
LEAPSECS@leapsecond.com
https://pairlist6.pair.net/mailman/listinfo/leapsecs