Contemplating the beginning and ending of time can be a complicated exercise in the mathematics of cosmology. Within the realms of an Oracle database though it’s much easier to get our heads around.
By definition within Oracle, the beginning of time is -4712-01-01 00:00:00.
The end of time is 9999-12-31 23:59:59.999999999. Thus all of time, past and future, is a little over 5 million days. That seems small enough for modern computing to keep things well under control.
Despite this seemingly simplified model, sometimes things go awry anyway. Several years ago I wrote about impossible timestamp values that showed up in an application table. Those were obviously corrupt values, being from the year 12800; but strangely, still semi-accessible. A couple years ago I also wrote about holes in the Oracle timeline where year 0 or calendar changes will affect date-time math in possibly unexpected ways.
Another oddity of time, or more accurately, time recording is the concept of time zones. These are especially tricky in that these zones change over time, especially with adoption and/or rejection of daylight saving time.
Timezones also allow us to partially break the limits of the Oracle timeline. Let’s look at the last second of Oracle time: 9999-12-31 23:59:59. Every point on Earth will see that second tick by but time zones mean some places will experience that tick of the clock before or later than other places. The islands of Samoa will be among the first to reach the end of time. By specifying the timezone we can identify that timestamp explicitly: 9999-12-31 23:59:59 Pacific/Apia. We can create such a value in Oracle and query. Depending on how your format your time zone information it may specify the name or the offset from UTC.
SQL> select timestamp '9999-12-31 23:59:59 Pacific/Apia' from dual; TIMESTAMP'9999-12-3123:59:59PACIFIC/APIA' --------------------------------------------------------------------------- 9999-12-31 23:59:59.000000000 Pacific/Apia SQL> select timestamp '9999-12-31 23:59:59 Pacific/Apia' from dual; TIMESTAMP'9999-12-3123:59:59PACIFIC/APIA' --------------------------------------------------------------------------- 9999-12-31 23:59:59.000000000 +14:00
Letting the Earth spin on its axis, we can wrap around and find the American Samoa islands will be among the last places to have their clocks tick the Oracle end of time.
SQL> select timestamp '9999-12-31 23:59:59 US/Samoa' from dual; TIMESTAMP'9999-12-3123:59:59US/SAMOA' --------------------------------------------------------------------------- 9999-12-31 23:59:59.000000000 US/Samoa SQL> select timestamp '9999-12-31 23:59:59 US/Samoa' from dual; TIMESTAMP'9999-12-3123:59:59US/SAMOA' --------------------------------------------------------------------------- 9999-12-31 23:59:59.000000000 -11:00
These aren’t just two different ways of writing the same point in time, they do in fact differ in actual time and Oracle can perform that math to show us the difference.
SQL> select timestamp '9999-12-31 23:59:59 US/Samoa' 2 - timestamp '9999-12-31 23:59:59 Pacific/Apia' time_diff 3 from dual; TIME_DIFF ------------------------------------------------------------------- +000000001 01:00:00.000000000
But, here we run into a temporal quandary. If Apia, Samoa is at the end of time, how is it 25 hours later we can still have another legal end of time?
And, if Pago Pago, American Samoa is ticking the end of time, then what time is it in Apia, Samoa?
Unfortunately, here we run into some limitations of SQL*Plus. While we can use the “AT TIME ZONE” functionality to convert US/Samoa to Pacific/Apia time, SQL*Plus won’t allow to display the result and returns no rows. This is not entirely surprising because Apia time is beyond the end of time, so how could Oracle know what that value is?
SQL> select timestamp '9999-12-31 23:59:59 US/Samoa' at time zone 'Pacific/Apia' from dual; ERROR: ORA-01877: string is too long for internal buffer no rows selected
But, it turns out Oracle can do the conversion, and does know what lies beyond its own end of time!
Before digging into Oracle’s mystery values. Let’s take Oracle out of the picture for a second and do the date math ourselves, we are not limited by Oracle’s calendar definition. What is 25 hours after 9999-12-31 23:59:59? 25 hours after the last second of December 31 is 00:59:59 of of January 2 in the year 10000, or 10000-01-02 00:59:59.000000000.
So, now going back to Oracle, even if it can’t display the full timestamp, does it know what year that timestamp value is that should be impossible? Yes, it does in fact calculate a timestamp value into the year 10000.
SQL> SELECT EXTRACT(YEAR FROM apiatime)
2 FROM (SELECT TIMESTAMP '9999-12-31 23:59:59 US/Samoa'
3 AT TIME ZONE 'Pacific/Apia' apiatime
4 FROM DUAL);
EXTRACT(YEARFROMAPIATIME)
-------------------------
10000
It also knows the calendar has wrapped around to a new year and it’s January.
SQL> SELECT EXTRACT(YEAR FROM apiatime), EXTRACT(MONTH FROM apiatime)
2 FROM (SELECT TIMESTAMP '9999-12-31 23:59:59 US/Samoa'
3 AT TIME ZONE 'Pacific/Apia' apiatime
4 FROM DUAL);
EXTRACT(YEARFROMAPIATIME) EXTRACT(MONTHFROMAPIATIME)
------------------------- --------------------------
10000 1
So this is looking promising, but then the math beyond the end of time gets fuzzy and Oracle does not calculate the day and hour correctly. Instead of hour 0 of day 2, it shows up as hour 10 of day 1.
SQL> SELECT EXTRACT(DAY FROM apiatime),
2 EXTRACT(HOUR FROM apiatime)
3 FROM (SELECT TIMESTAMP '9999-12-31 23:59:59 US/Samoa'
4 AT TIME ZONE 'Pacific/Apia' apiatime
5 FROM DUAL);
EXTRACT(DAYFROMAPIATIME) EXTRACT(HOURFROMAPIATIME)
------------------------ -------------------------
1 10
Looking at the other end of time, Oracle’s beginning, things get even weirder. Of course the planet was rotating 6700 years ago, but nobody was defining time zone standards then. So, while Oracle will allow you to construct a timestamp with time zone value using any of the defined time zones, does the result really mean anything at the beginning of time? Part of me says no for historical accuracy; but part of me says yes, because it’s just math. Even if the time zones are arbitrary, the arithmetic of offsets should be calculable even if not historically meaningful.
But, when we get near the beginning of time, the math doesn’t appear to work reliably. Using our Pacific islander neighbors again, we would expect the time difference to be 25 hours because the offsets are still -11 and +14. That is not the result we get in our calculated time difference though.
SQL> select timestamp '-4712-01-01 00:00:00 Pacific/Apia' 2 - timestamp '-4712-01-01 00:00:00 US/Samoa' time_diff 3 from dual; TIME_DIFF ------------------------------------------------------------------ +000000000 00:04:00.000000000
The reason for the difference being only 4 minutes is the offsets are not reliable going backwards in time.
SQL> select timestamp '-4712-01-01 00:00:00 Pacific/Apia' 2 , timestamp '-4712-01-01 00:00:00 US/Samoa' 3 from dual; TIMESTAMP'-4712-01-0100:00:00PACIFIC/APIA' TIMESTAMP'-4712-01-0100:00:00US/SAMOA' -------------------------------------------- -------------------------------------- -4712-1-2 0:0:0.000000000 +12:33 -4712-1-2 0:0:0.000000000 +12:37
While the specific results are likely not expected; this does coincide with the previous observation that no time zones were defined back then so it doesn’t make much sense to use them. I think I would have preferred Oracle simply raise an exception or return NULL rather than apply strange offsets that give strange answers.
But, if named time zones weren’t defined; what if we cheat and specify our own offsets. Then the math will still be clear cut, won’t it? Using the +14 and -11 offsets we expect the difference of two timestamps to be 25 hours; but math that extends through the beginning of time is also unreliable, even with fixed offsets.
SQL> select timestamp '-4712-01-01 00:00:00 +14:00' 2 - timestamp '-4712-01-01 00:00:00 -11:00' time_diff 3 from dual; TIME_DIFF --------------------------------------------------------------- -000000000 01:00:00.000000000
Instead of 25 hours, we get just 1, so the beginning of time is also a barrier we should not breach if we want our date-time math to work as expected.
For most real-world data I imagine these temporal extremes won’t matter much; but that doesn’t mean they can be completely ignored though.
I didn’t find these by intentionally looking for strange anomalies. I encountered these quirks in real code. I first noticed them while examining real business logic with real business data. While trying to track down an application bug I happened across some erroneous values that produced extreme date values. Even though the dates themselves were incorrect, I also saw that the math wasn’t working. While I was concerned with the erroneous input values, I was also thinking even if the dates are wrong, the math should still work, so I dug into that function looking for where the calculations were going awry. Once I isolated the specific lines where the results weren’t correct I saw the problems happened when math crossed the beginning of time. After recognizing that I tested the other extreme with year 9999 and saw further errors.
So, I added assertions in the function to ensure only mathematically viable dates would be processed and having ameliorated that issue, then backtracked to the calling process to determine where the erroneous date and timestamp input values were originating.
As I said at the beginning I find time interesting, so these limits of date-time math were fun to explore. One of my friends at work is fond of saying “Dates are hard” – when the extremes make your math go awry – they get even harder. Hopefully this exercise will help soften the impact if/when you run into either end of the Oracle calendar.