Database Reference
In-Depth Information
EODA@ORA12CR1> insert into t (what, x)
2 select 'minute', trunc(x,'mi') from t
3 union all
4 select 'day', trunc(x,'dd') from t
5 union all
6 select 'month', trunc(x,'mm') from t
7 union all
8 select 'year', trunc(x,'y') from t
9 /
4 rows created.
EODA@ORA12CR1> select what, x, dump(x,10) d from t;
WHAT X D
-------- --------- -----------------------------------
orig 25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
minute 25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
day 25-JUN-05 Typ=12 Len=7: 120,105,6,25,1,1,1
month 01-JUN-05 Typ=12 Len=7: 120,105,6,1,1,1,1
year 01-JAN-05 Typ=12 Len=7: 120,105,1,1,1,1,1
To truncate that date down to the year, all the database had to do was put 1s in the last 5 bytes—a very fast
operation. We now have a sortable, comparable
DATE
field that is truncated to the year level, and we got it as efficiently
as possible.
Adding or Subtracting Time from a DATE
A question I am frequently asked is, “How do I add time to or subtract time from a
DATE
type?” For example, how do
you add one day to a
DATE
, or eight hours, or one year, or one month, and so on. There are three techniques you'll
commonly use:
NUMBER
to the
DATE
. Adding 1 to a
DATE
is a method to add 1 day. Adding 1/24 to a
DATE
therefore adds 1 hour, and so on.
•
Simply add a
INTERVAL
type, as described shortly, to add units of time.
INTERVAL
types
support two levels of granularity: years and months, or days/hours/minutes/seconds. That is,
you may have an interval of so many years and months
or
an interval of so many days, hours,
minutes and seconds.
•
You may use the
•
Add months using the built-in
ADD_MONTHS
function. Since adding a month is generally not as
simple as adding 28 to 31 days, a special purpose function was implemented to facilitate this.
Table
12-3
demonstrates the techniques you would use to add (or subtract, of course) N units of time to a date.
