Figure 10-1. Effect of different datetime datatypes

The figure shows the user Jonathan in the Eastern Time Zone, which is five hours behind Coordinated Universal Time (UTC). (See the sidebar for a description of UTC.) Jonathan stores the same datetime value in four database fields. The datetime value (in the figure) is represented using ANSI/ISO standard notation, and in this case represents 3:00 PM (15:00:00.00) Eastern Standard Time (-5:00) on Feb 6, 2002 (2002-02-06).

The database in the figure is in the Mountain Standard Time Zone. Notice how the database representation varies with each of the different datetime datatypes. DATE and TIMESTAMP totally ignore the time zone difference between the user and the database. They also don't preserve the original time zone, so that information is lost along with any knowledge of the time the user really was referring to.

The TIMESTAMP WITH TIME ZONE column preserves the time zone information and represents the exact time, from his point of view, that Jonathan entered the values. Compare this with the behavior of the TIMESTAMP WITH LOCAL TIME ZONE column. Here, you see that the time has been converted from Eastern Time into Mountain Time, the database's local time zone. The correct time has been preserved, but the point of view has been lost; we no longer know the time zone in which the time was entered.

Figure 10-1 is conceptual with respect to TIMESTAMP WITH TIME ZONE. Internally, Oracle represents all TIMESTAMP WITH TIME ZONE values in UTC time. This is no doubt to make date comparisons and arithmetic efficient. With respect to the figure, the internal representation would be: 2002-02-06 20:00:00:00.00 -5:00 The time zone information is preserved so the datetime can be properly converted back into its original time zone, and you are never "aware" that UTC is used internally.

 

Finally, look at the user Donna in Figure 10-1. Donna, like the database, is in the Mountain Standard Time Zone. Notice the values she gets back when she queries the database for the four values Jonathan entered. The DATE and TIMESTAMP values are completely misleading. Jonathan entered a value at 3:00 PM Eastern Time, and Donna now sees that as 3:00 PM Mountain Time. The situation is much better with the other datatypes. Donna sees the TIMESTAMP WITH TIME ZONE value exactly as it was originally entered, and can see that Jonathan entered an Eastern Standard Time value. Donna sees the correct time for the TIMESTAMP WITH LOCAL TIME ZONE value (1:00 PM Mountain Time is equivalent to 3:00 PM Eastern Time), but she has no idea what time zone was used to enter the value originally.

Coordinated Universal Time Coordinated Universal Time, abbreviated UTC, is measured using highly accurate and precise atomic clocks, and forms the basis of our worldwide system of civil time. Time zones, for example, are all defined with respect to how far they are in terms of hours and minutes from UTC. UTC is atomic time, and is periodically adjusted through the mechanism of leap seconds to keep it in sync with time as determined by the rotation of the earth. You may be familiar with Greenwich Mean Time (GMT). GMT is an obsolete term in scientific circles, but is often used colloquially. For most practical purposes, GMT is equivalent to UTC, but the two are not really the same. Why the acronym UTC and not CUT? The standards body couldn't agree on whether to use the English acronym CUT or the French acronym TUC, so they compromised on UTC, which matches neither language. See http://www.boulder.nist.gov/timefreq/general/misc.htm#Anchor-14550. For more information on UTC, see the U.S. Naval Observatory document "What is Universal Time?" at http://aa.usno.navy.mil/faq/docs/UT.html. Also see the National Institute of Standards and Technology document on UTC at http://physics.nist.gov/GenInt/Time/world.html.