Most databases live and work in one time zone. If you have a database that covers more than one time zone, you might consider storing time in UTC and adding a numeric column to hold the local time-zone offset.The time zones start at UTC, which has an offset of zero. This is how the system-level time-zone table in Standard SQL is defined.
- There are also ISO-standard three-letter codes for the time zones of the world, such as EST, for Eastern Standard Time, in the United States.
- The offset is usually a positive or negative number of hours, but there were some odd zones that differed by 15 minutes from the expected pattern; these were removed in 1998.
- Now you have to factor in daylight saving time on top of that to get what is called “lawful time,” which is the basis for legal agreements.
(This is not a problem for them, since they also use a comma for a decimal point.) Most databases give you these display options.
One of the major problems with time is that there are three kinds— fixed events (“He arrives at 13:00”), durations (“The trip takes three hours”), and intervals (“The train leaves at 10:00 and arrives at 13:00”)—which are all interrelated.
- Standard SQL introduces an INTERVAL data type that does not explicitly exist in most current implementations (Rdb, from Oracle Corporation, is an exception).
- An INTERVAL is a unit of duration of time, rather than a fixed point in time—days, hours, minutes, seconds. There are two classes of intervals. One class, called year-month intervals, has an express or implied precision that includes no fields other than YEAR and MONTH, though it is not necessary to use both.
- The other class, called day-time intervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH—that is, DAY, HOUR, MINUTE, and SECOND (with decimal places).