Photo by Jenny Rollo
Written by John Maenpaa, Senior Manager, Health Care Service Corporation. johnmaenpaa@db2solutions.org
With the recent switch over to daylight savings time, I’ve been giving some thought to the idea that we should consider our timestamps more carefully.
Until now, most of us have just stored our timestamps in the database and ignored any attempts to display information based on the location of the user. The people that use our systems get used to the idea that the computer is in New York (or some other location) and the times they see reflect that. Should we become more user friendly and try to display our timestamps in the local time zone? It is certainly worth considering.
In DB2 for z/OS, version 10 finally delivered the ability to attach time zones to our timestamp columns. The CURRENT TIMESTAMP special register received additional clauses to let us get the CURRENT TIMESTAMP WITH TIME ZONE. Additional features let us pull timestamps from our tables with specific local time or a different time zone’s value. Sounds good so far.
This works by storing an additional component to the timestamp field. This component is the offset from UTC for the date/time. The timestamp with time zone for noon on April 1st in Chicago would be '2014-04-01-12.00.00.000000-5:00'. The time zone component contains the difference between our local time (12:00 PM) and UTC (17:00 PM). You can calculate UTC by subtracting the time zone from the local timestamp. Some time zones around the world can shift by minutes, so the time zone component contains hours and minutes. When stored, a column that is a timestamp with time zone takes two bytes more than the same timestamp without time zone.
In some application designs, we end up with tables that need to be partitioned by an increasing key value. This could be a number generated using a sequence or it could be a timestamp. If you use a timestamp, the switch to (or from) daylight savings time can have an impact on your data. Most programmers don’t spend a lot of time thinking about time itself and the fact that it can jump forward or backward. The time change periods are just one example where this happens. The Network Time Protocol (NTP) used to keep the clocks in sync on most UNIX servers has an impact on time as well, adjusting the local system clock on a regular basis to try to keep it fairly accurate. On z/OS systems, we use Sysplex Timers or the Server Time Protocol (STP) to do the same thing. Whenever the time gets adjusted backward we run into a situation where the new key values may actually precede prior key values. This shouldn’t a big deal as long as you keep in mind the idea that time doesn’t always move forward.
Now we can add a time zone to our increasing key values. When the time changes and the clock moves backward an hour, the time zone would also change. This should get us a little closer continuous flow of time. Here in Chicago we switch from daylight savings time to standard time in the fall, so our clocks will tick over from '2014-11-02-01.59.59.999999-5:00' to '2014-11-02-01.00.00.000000-6:00'. You can see the time component of the timestamp falls back from 1:59 AM to 1:00 AM and the time zone component also shifts an hour back. When you ask DB2 to perform comparisons on timestamps with time zones, it converts them to UTC in order to perform the comparison. If the two timestamps have the same UTC value, they are considered equal. This means that in our shift to standard time this fall, the timestamp continues to move forward when we set our clocks backward.
With our newly acquired ability to take time zones into account, should we now use timestamps with time zones for our increasing key values? Probably not. There is a limitation in the use of timestamps with time zones related to indexes. You cannot use a timestamp with time zone in an index that uses either PARTITION or PARTITION BY RANGE. If you are creating a table with the ever increasing timestamp, then you are probably going to want it partitioned at some point. This restriction applies to any timestamp column you may want to include in the index.
But what happens if we don’t use a time zone in our timestamps? If you compare a timestamp without time zone to a timestamp with time zone, DB2 must add time zone information so they can both be converted to UTC. There is a CURRENT TIME ZONE special register that is subtracted from the timestamps that don’t have a time zone in order to make that conversion. How does that work in relation to daylight savings time? We’ll have to try it and find out.
Before you decide to store a time zone with your timestamp, make sure it is what you really need.