|
Working with Timestamps
We all love the DATE datatype, but let's face it. It wasn't everything we always
wanted in a timestamp datatype. Namely, the DATE datatype.
* Supported a timestamp only down to the nearest second. Perhaps when the Oracle
database was first designed and released (in the early 1980s), that was good
enough. But now we're on "Internet time," and fractions of seconds are the cat's
meow.
* Offered almost no support for time zone manipulation. The NEW_TIME
function acted as though it would allow you to work with different time zones,
but it was just a stopgap.
Oracle has corrected these deficiencies in Oracle 9i by introducing the
TIMESTAMP datatype. With TIMESTAMP, you can specify a precision (up to nine
digits) for fractions of seconds. And you can take advantage of smart, built-in
time zone recognition, manipulation, and arithmetic. Let's look at some
examples.
Here's a declaration of a TIMESTAMP with a precision down to a thousandth of a
second:
DECLARE
test_endpoint TIMESTAMP(3);
BEGIN
test_endpoint :=
'1999-06-22 ' ||
'07:48:53.275'; |
I assign a value to that checkout timestamp through an implicit conversion.
This is very similar to the type of code one might write to assign a value to a
DATE variable, except that I can now also provide a fractional value for the
second (275/1000).
Of course, for the most part, we won't be assigning fractional components of
seconds. Instead, such information will be taken from system timestamp
information or provided from externally-generated data (from, say, a
manufacturing assembly line).
Oracle provides a host of new built-in functions to obtain and convert
timestamps, as I demonstrate in the following script:
DECLARE
-- Grab the current timestamp,
-- restricting precision to
-- four digits
right_now TIMESTAMP (4) :=
CURRENT_TIMESTAMP;
-- Grab the current timestamp,
-- but preserve time zone info.
over_there TIMESTAMP (0)
WITH TIME ZONE:=
CURRENT_TIMESTAMP;
-- Use LOCAL TIME ZONE with
-- the timestamp
right_here TIMESTAMP (2)
WITH LOCAL TIME ZONE:=
CURRENT_TIMESTAMP;
BEGIN
-- Display the values
DBMS_OUTPUT.put_line (
SYSTIMESTAMP);
DBMS_OUTPUT.put_line (
CURRENT_TIMESTAMP);
DBMS_OUTPUT.put_line (
right_now);
DBMS_OUTPUT.put_line (
over_there);
DBMS_OUTPUT.put_line (
right_here);
END;
And here's the output displayed:
SYSTIMESTAMP
05-FEB-02 12.57.44.000000000 PM -08:00
CURRENT_TIMESTAMP
05-FEB-02 12.57.44.000000107 PM -08:00
TIMESTAMP (4)
05-FEB-02 12.57.44.0000 PM
TIMESTAMP (0) WITH TIME ZONE
05-FEB-02 12.57.44 PM -08:00
TIMESTAMP (2) WITH TIME ZONE
05-FEB-02 12.59.59.00 PM |
Working with time zones can get very complicated, and Oracle documentation is
still a bit on the skimpy side. You'll need to make sure that your database has
set a time zone, which isn't done by default. Here's the kind of statement you'd
execute (and then restart the database):
| ALTER DATABASE SET time_zone = 'US/Central' |
You can also set a time zone for a session as well, such as:
| ALTER SESSION SET time_zone = 'US/Central' |
You can also set the default time zone format used for conversion and display
as follows:
ALTER SESSION
SET NLS_TIMESTAMP_TZ_FORMAT =
'DD-Mon-YYYY HH24:MI:SSXFF TZR TZD'; |
You can examine the full (and greatly expanded) set of Oracle-recognized time
zones with the following query:
| SELECT DISTINCT tzname FROM v$timezone_names; |
Example 1 below offers a procedure that you can use to set the time zone in
your session and then display various elements of the current time zone
information.
Example 1. Set and Show Time Zone Information
CREATE OR REPLACE PROCEDURE tz_set_and_show (tz_in IN
VARCHAR2 := null)
IS
BEGIN
IF tz_in IS NOT NULL
THEN
EXECUTE IMMEDIATE 'alter session set time_zone = '''
|| tz_in
|| '''';
END IF;
DBMS_OUTPUT.put_line ( 'SESSIONTIMEZONE = '
|| SESSIONTIMEZONE);
DBMS_OUTPUT.put_line ( 'CURRENT_TIMESTAMP = '
|| CURRENT_TIMESTAMP);
DBMS_OUTPUT.put_line ( 'LOCALTIMESTAMP = '
|| LOCALTIMESTAMP);
DBMS_OUTPUT.put_line (
'SYS_EXTRACT_UTC (LOCALTIMESTAMP) = '
|| sqlexpr ('SYS_EXTRACT_UTC (LOCALTIMESTAMP)')
);
END;
/ |
Working with Intervals
Use variables of type INTERVAL to store and manipulate deltas between two
different dates or timestamps. In the past, you would've treated this same
information with a number, but then you'd have to interpret that number in ways
that greatly increased the possibility of error and greatly decreased the
readability (and therefore, maintainability) of your code.
Now, with INTERVALs, you can write very understandable code that allows for
manipulation of timestamp deltas in natural, intuitive ways.
Recognizing that there are roughly two "scales" of intervals with which humans
concern themselves, you can declare and use INTERVALs of type YEAR TO DAY and
DAY TO SECOND.
With YEAR TO DAY INTERVALs, you can store and manipulate intervals of years and
months. The syntax for this interval is:
INTERVAL YEAR[(precision)] TO MONTH
where the precision can range from 0 to 4, with a default of 2. You don't get to
specify a precision for MONTH.
Use DAY TO SECOND INTERVALs to store and manipulate intervals of days, hours,
minutes, and seconds. With this interval type, you can set two levels of
precision:
INTERVAL DAY[(leading_precision)]
TO
SECOND[(fractional_seconds_precision)]
The default values are 2 and 6, respectively. You must use integer literals in
these declarations; you may not use a variable or named constant.
Suppose I've created a person object type. I add a member procedure to calculate
the age of a person. For such a calculation, I really don't need to get too
detailed; number of years and months is fine. So I define this function as
follows:
MEMBER FUNCTION age RETURN INTERVAL YEAR TO MONTH
IS
retval INTERVAL YEAR TO MONTH;
BEGIN
RETURN (SYSDATE - SELF.dob)
YEAR TO MONTH;
END; |
Notice that I perform date arithmetic between today's date and the date of
birth (dob) of the currently instantiated object (SELF). I then express that
delta as an INTERVAL.
Oracle is very flexible about allowing you to specify intervals, as shown in the
following set of assignments. In the following block, I assign my duration of 14
years, seven months working with Oracle technology (I started with Oracle
Corporation in August 1987 and lasted five years!) to an INTERVAL variable:
DECLARE
oracle_career
INTERVAL YEAR(2) TO MONTH;
BEGIN
-- Example of INTERVAL literal
oracle_career :=
INTERVAL '14-7' YEAR TO MONTH;
-- Implicit conversion from string
oracle_career := '14-7';
-- Assign year and month components
-- individually
oracle_career := INTERVAL '14' YEAR;
oracle_career := INTERVAL '7' MONTH;
END;
/ |
|
