Database Reference
In-Depth Information
■
you may get slightly different results if the date was stored when standard time zone was in effect and then
retrieved when Daylight savings time is in effect. the output in the prior example would show a two-hour difference
instead of what you would intuitively think would be a one-hour difference. I only point this out to drive home the fact that
time-zone math is much more complex than it appears!
Note
The
TIMESTAMP WITH LOCAL TIME ZONE
provides sufficient support for most applications, if you need not
remember the source time zone, but only need a datatype that provides consistent worldwide handling of date/time
types. Additionally, the
TIMESTAMP(0) WITH LOCAL TIMEZONE
provides you the equivalent of a
DATE
type with time
zone support—it consumes 7 bytes of storage and the ability to have the dates stored normalized in UTC form.
One caveat with regard to the
TIMESTAMP WITH LOCAL TIME ZONE
type is that once you create tables with this
column, you will find your database's time zone is frozen—and you will not be able to change it:
EODA@ORA12CR1> alter database set time_zone = 'PST';
alter database set time_zone = 'PST'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has
TIMESTAMP WITH LOCAL TIME ZONE columns
EODA@ORA12CR1> !oerr ora 30079
30079, 00000, "cannot alter database timezone when database has
TIMESTAMP WITH LOCAL TIME ZONE columns"
// *Cause: An attempt was made to alter database timezone with
// TIMESTAMP WITH LOCAL TIME ZONE column in the database.
// *Action: Either do not alter database timezone or first drop all the
// TIMESTAMP WITH LOCAL TIME ZONE columns.
It should be obvious why: if you were to change the database's time zone, you would have to rewrite every single
table with a
TIMESTAMP WITH LOCAL TIME ZONE
because their current values would be wrong, given the new time zone!
INTERVAL Type
We briefly saw
INTERVAL
type used in the previous section. It is a way to represent a duration of time or an interval of
time. There are two interval types we'll discuss in this section: the
YEAR TO MONTH
type, which is capable of storing a
duration of time specified in years and months, and the
DAY TO SECOND
type, which is capable of storing a duration of
time in days, hours, minutes, and seconds (including fractional seconds).
Before we get into the specifics of the two
INTERVAL
types, I'd like to look at the
EXTRACT
built-in function, which
can be very useful when working with this type. The
EXTRACT
built-in function works on
TIMESTAMP
s and
INTERVAL
s,
and it returns various bits of information from them, such as the time zone from a
TIMESTAMP
or the hours/days/
minutes from an
INTERVAL
. Let's use the previous example, where we got the
INTERVAL
of 380 days, 10 hours,
20 minutes, and 29.878 seconds:
EODA@ORA12CR1> select dt2-dt1
2 from (select to_timestamp('29-feb-2000 01:02:03.122000',
3 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
4 to_timestamp('15-mar-2001 11:22:33.000000',