On Wed, Oct 19, 2016 at 11:59 AM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:
The database people seem to have the weird rules for the date part of a
time stamp implemented. This argues that they ought to be able to
implement the weird rules for the time portion as well.
Ah, but the date part follows rules. Moreover, these rules are 100%
deterministic.
Leap seconds are unpredictable. There is no one in the universe who
can tell you with certainty TODAY whether 2018-12-31-23.59.60 will be
a valid timestamp or not.
ISO 8601 allows for leap seconds, just as it allows for leap years.
Does DB2 have a 'we don't need to store legitimate but rare values'
exemption? I didn't see one.
You're asking the question in the wrong direction. Do you want DB2 to
be able to store illegitimate values? Lots of them?
If we have logic (whether in a program or database) which says "months
can have up to 31 days", does that mean you want to allow February 31?
Why not use a plain 8-digit numeric to store dates? That will
certainly handle every possible valid date value! (At least for
several thousand more years.)
Typically for me, I used a stupid example. Let's say that a trading
partner sends me a transaction and it's stamped 2016-12-31-23.59.60 UTC.
I didn't generate it, I don't have control over the stamps they send
me. What should I do with that transaction?
Alter the incoming timestamp?
Reject the transaction?
Given a timestamp data type that doesn't allow storage of leap
seconds, I would alter the timestamp value to 2016-12-31-23.59.59 UTC.
(This is what I was suggesting in my last post.) This way you can do
any kind of calculation you would otherwise do, and in absolute terms
you would be off by at most 1 second. For 99.idontknowhowmanynines% of
businesses, this is plenty good enough.
Right now, the least worst answer seems to be that I should stop using
TIMESTAMP data types and revert to two separate columns instead: a DATE
and a TIME (which I can NULL out 'rare but legitimate' values of)? At
least then I can store the date part which will (mostly) let me answer
questions like 'how old is this invoice?'
If you want to get the most benefit out of the existing data types,
but still be as rigorous as possible, then the thing to do is to keep
using TIMESTAMP (using the special truncation to 59 as necessary), but
also have another field which just serves as a flag to indicate
whether the timestamp was a leap second or not. Most applications can
ignore this flag. Applications that care and know what to do with it
can consult it.
All that said, plenty of systems do choose the permissive route,
allowing the equivalent of inputting "February 31", but with regards
to times instead of dates. Whether you can get February 31 back out is
another matter. Usually you can't. You'll normally get March 3 (or
March 2 on a leap year). (I'm talking in analogies here. For systems
that allow 23.59.60, this is usually converted to what amounts to
00.00.00 of the next day.) This is because internally, timestamps are
most often stored as seconds-since-epoch. (Notably, C and POSIX
accommodate up to TWO leap seconds EVERY HOUR.)
The permissive stance does not make for a good situation when it comes
to calculations. It means that a leap second, when being read back
out, might give you a different DATE than what you put in. (This is
why I recommend using 23.59.59 (again) for the leap second; that way
the date portion isn't screwed up.)
Tellingly, Oracle, Microsoft SQL Server, PostgreSQL, and MySQL all
disallow values above 59 for the second. So, would you rather have
DB2's timestamp be more like C's and POSIX's, or more like all the
other big-name databases in the world?
Interesting reading pertaining to the "extended 23.59.59" model of leap seconds:
<
http://dev.mysql.com/doc/refman/5.7/en/time-zone-leap-seconds.html>
<
https://www.ibm.com/developerworks/community/blogs/IMSupport/entry/weekly_tips_from_db2_experts_what_is_the_impact_of_leap_second_on_db2?lang=en>
One takeaway from reading about this stuff (including a lot that I
didn't link to here): leap seconds are most appropriately handled at
the operating system level. The system clock should shield everything
else from having to worry about leap seconds. If you have an
application which is sensitive enough to notice that the system clock
has been adjusted FOR ANY REASON, then whatever that application is
doing to cope with that adjustment, that same mechanism is what copes
with leap second adjustments.
John Y.
midrange.com
