Sunday 20 July 2014

Temporal's Infinity

Here is some interesting trivia, lets say you have created a system temporal table, and added in some data, you will see your start and end date automatically updated.
Now clearly the start time, will be the instant the data was added, but the end date, well that is conceptually debatable, since we don't have an end date yet, we will only have that value at some point in the future... or perhaps never. So we need something to represent infinity.

However, in reality, we have little choice, since our schema is set in place, we have to pick something to represent infinity, I guess the two options would have been the largest possible date you can represent, or NULL.

Some what surprisingly, DB2 chose neither of these.. check it out :

tb_YourTable
------------
MY_DATA     SYS_START                        SYS_END
----------- -------------------------------- --------------------------------
          1 2014-07-19-12.14.16.100732000000 9999-12-30-00.00.00.000000000000

I know what you are about to say, "yes they did choose one, they chose the largest representable date", but no, look more carefully.
No I am not being pedantic about the hours mins and seconds, although that would also count.. there is something more blatant.

How many days are there in December? Yes, 31 hmmm interesting they chose 30 - but why, an oversight they just never bother to fix?

Thankfully not, it appears to have more intelligence than that, and it also explains why the date is also at the beginning of the day, not midnight.

Some languages can only represent years up to 9999, and then add into that the issues of timezones, it would theoretically be possible for the value that represents INFINITY to be read into a structure, adjusted for time zones, and then wrap beyond the end... in effect breaking the temporal table.
By giving yourself a full day, you eliminate this issue.

When will this information be useful to you ?... umm don't know, perhaps a trivia game, but hey nice to know anyway :)

No comments:

Post a Comment