Welcome to the next installment of 9i new features, topic for today is:
DateTime datatypes and the functions that support them.
Here are the 4 new data types that will be covered:
TIMESTAMP
TIMESTAMP WITH [LOCAL]TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
The functions:
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT
FROM_TZ
LOCALTIMESTAMP
NUMTOYMINTERVAL
INTERVAL YEAR to MONTH
NUMTODSINTERVAL
INTERVAL DAY TO SECOND
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSTIMESTAMP
TO_DSINTERVAL
TO_TIMESTAMP
TO_YMLITERAL
TZ_OFFSET
--- Datatypes:
The TIMEZONE question seems to come up alot more recently on the "list",
and how can I get fractional seconds?
Here is a short chart to help out with "is timezone included in this
datatype?".
Datatype Time Zone Fractional Seconds
-------------------------------------------------------------------
DATE No No
TIMESTAMP(PRECISION) No Yes
TIMESTAMP WITH TIME ZONE Explicit Yes
TIMESTAMP WITH LOCAL TIME ZONE Relative Yes
So when do you use which datatype?
Use the DATE datatype to store point-in-time values (dates and times) in
a
table. The DATE datatype stores the century, year, month, day, hours,
minutes,
and seconds.
Use the TIMESTAMP datatype to store precise values, down to fractional
seconds.
For example, an application that must decide which of two events
occurred
first might use TIMESTAMP. An application that needs to specify the time
for
a job to execute might use DATE.
The default timestamp format is set by the Oracle initialization
parameter
NLS_TIMESTAMP_FORMAT.
Because TIMESTAMP WITH TIME ZONE can also store time zone information,
it is
particularly suited for recording date information that must be gathered
or coordinated across geographic regions.
The datatype TIMESTAMP WITH TIME ZONE, which extends the datatype
TIMESTAMP,
includes a time-zone displacement. The time-zone displacement is the
difference (in hours and minutes) between local time and
Coordinated Universal Time (UTC)--formerly Greenwich Mean Time.
Use TIMESTAMP WITH LOCAL TIME ZONE values when the time zone is not
significant. For example, you might use it in an application that
schedules
teleconferences, where each participant sees the start and end times for
their own time zone.
The TIMESTAMP WITH LOCAL TIME ZONE type is also appropriate for two-tier
applications where you want to display dates and times using the time
zone of
the client system. You should not use it in three-tier applications,
such
as those involving a web server, because in that case the client is the
web server, so data displayed in a web browser is formatted according to
the time zone of the web server rather than the time zone of the
browser.
Use INTERVAL DAY TO SECOND to represent the precise difference between
two
datetime values. For example, you might use this value to set a reminder
for
a time 36 hours in the future, or to record the time between the start
and
end of a race. To represent long spans of time, including multiple
years,
with high precision, you can use a large value for the days portion.
Use INTERVAL YEAR TO MONTH to represent the difference between two
datetime
values, where the only significant portions are the year and month.
For example, you might use this value to set a reminder for a date 18
months
in the future, or check whether 6 months have elapsed since a particular
date.
-- FUNCTIONS
CURRENT_DATE: returns the current date in the session timezone.
CURRENT_TIMESTAMP: returns the current date and time in the session
time
zone, in a value of datatype TIMESTAMP WITH TIME ZONE. This is the one
to use
instead of sysdate.
DBTIMEZONE: returns the value of the database timezone, this value
will depend on how the DBA specified the time zone value either during
the
create database or alter database statement.
EXTRACT: returns the value of a specified datetime field from a
datetime
or interval value expression. This one will be with an example also.
EXTRACT
( { { YEAR
| MONTH
| DAY
| HOUR
| MINUTE
| SECOND
}
| { TIMEZONE_HOUR
| TIMEZONE_MINUTE
}
| { TIMEZONE_REGION
| TIMEZONE_ABBR
}
}
FROM { datetime_value_expression | interval_value_expression }
The following example returns the number 1998.
SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;
EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
1998
FROM_TZ: converts a timestamp value to a TIMESTAMP WITH TIME ZONE
value.
LOCALTIMESTAMP: returns the current date and timein the session
time
zone, the return type is TIMESTAMP, so whats the difference between this
and
current_timestamp? Well it all has to do with the timezone piece, here
is an
example:
SQL> alter session set time_zone = '-5:00';
Session altered.
SQL> select current_timestamp, localtimestamp from dual;
CURRENT_TIMESTAMP LOCALTIMESTAMP
---------------------------------------------------------------------------
15-NOV-01 12.16.11.137295 PM -05:00 15-NOV-01 12.16.11.137295 PM
SQL> alter session set time_zone = '-8:00';
Session altered.
SQL> select current_timestamp, localtimestamp from dual;
CURRENT_TIMESTAMP LOCALTIMESTAMP
---------------------------------------------------------------------------
15-NOV-01 09.16.11.151920 AM -08:00 15-NOV-01 09.16.11.151920 AM
Notice the only difference is one has timezone and the other doesn't.
NUMTOYMINTERVAL: converts a literal or variable to either years and
months. So the question is how many years and months ago was 85
months, it
is 7 years and 1 month.
SQL> select (numtoyminterval(85,'MONTH')* -1) from dual;
(NUMTOYMINTERVAL(85,'MONTH')*-1)
---------------------------------------------------------------------------
-000000007-01
NUMTODSINTERVAL: does conversion between a variable and converts
it
to the parameter you pass as the second field(DAY, HOUR, MINUTE,
SECOND), so
let's convert 85 hours to days:
SQL> select (numtodsinterval(85,'HOUR')* -1) from dual;
(NUMTODSINTERVAL(85,'HOUR')*-1)
---------------------------------------------------------------------------
-000000003 13:00:00.000000000
its 3 days and 13 hours.
SESSIONTIMEZONE: returns the value of the session's time zone.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00
SYS_EXTRACT_UTC: extracts the UTC, aka Coordinated Universal Time
from a
datetime with timezone displacement.
SQL> select sys_extract_utc(timestamp '2001-11-28 13:11:00.00 -05:00')
from dual
;
SYS_EXTRACT_UTC(TIMESTAMP'2001-11-2813:11:00.00-05:00')
---------------------------------------------------------------------------
28-NOV-01 06.11.00.000000000 PM
SYSTIMESTAMP: returns the system date, INCLUDING fractional
seconds and
timezone of the database. This could be used in place of sysdate if you
wanted to or needed fractional seconds. It returns a TIMESTAMP WITH
TIMEZONE
data type.
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
28-NOV-01 01.18.45.796541 PM -05:00
If you dont like the look of the format you have to change it via the
alter
session nls_timestamp_tz_format command.
alter session set nls_timestamp_tz_format = 'FORMAT STRING HERE';
Trivia: You can find the available names for time zones in the
TIMEZONE_REGION and TIMEZONE_ABBR columns of the V$TIMEZONE_NAMES data
dictionary view.
Wayy late on this one but have been extremely busy, this doesn't cover
all
of the data types but more of the most commonly used ones.
[EMAIL PROTECTED] for good/bad/otherwise info.
Joe
--
Joe Testa, Oracle DBA
Want to have a good time with a bunch of geeks? Check out:
http://www.geekcruises.com/standard_interface/future_cruises.html
I'm presenting, when registering drop my name :)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joe Testa
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
