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',
 
 
Search WWH ::




Custom Search