Oracle Scheduling Timestamps

Also see Oracle Current Timestamp

Timestamps

The TIMESTAMP datatype is an extension on the DATE datatype.  In addition to the datetime elements of the DATE datatype, the TIMESTAMP datatype holds fractions of a second to a precision between zero and nine decimal places, the default being six.  There are also two variants called TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.  As their names imply, these timestamps also store time zone offset information.

Like dates, timestamps are stored using a binary date format.  A TIMESTAMP is 11 bytes long; while those variants with time zone information require 13 bytes.  Table 3.5 below shows how each of the 11-13 bytes is used to store the timestamp information:

Table 3.5 - How the 11-13 bytes are used to store timestamp information

The following example uses the dump function to show the contents of a stored timestamp:

alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM';
drop table timestamp_test;
create table timestamp_test as
select
   systimestamp as now
from
   dual
;
select
   now,
   dump (now)
from
   timestamp_test
;

NOW
--------------------------------------------------------
DUMP(NOW)
--------------------------------------------------------
31-JUL-04 11.15.05.662509 +01:00
Typ=181 Len=13: 120,104,7,31,11,16,6,39,125,21,200,21,60

1 row selected.

Although they can look confusing due to the action of the offset, the first seven components match those of the DATE datatype. In this example, the offset of +01:00 makes the hour component appear to be in zero base notation, rather than excess-1; however, when the offset is added, it is clear to see that it is not..  The offset component represents the number of minutes the time is offset due to the time zone.

The 10g scheduler uses timestamps to define time-related information about scheduled jobs, as seen in the dbms_scheduler.create_schedule procedure and the dba_scheduler_schedules view shown below.

PROCEDURE create_schedule (
  schedule_name           IN VARCHAR2,
  start_date              IN TIMESTAMP WITH TIME ZONE  DEFAULT NULL,
  repeat_interval         IN VARCHAR2,
  end_date                IN TIMESTAMP WITH TIME ZONE  DEFAULT NULL,
  comments                IN VARCHAR2                  DEFAULT NULL)

SQL> describe dba_scheduler_schedules
 Name                       Null?    Type
 -------------------------- -------- -------------------------
 OWNER                      NOT NULL VARCHAR2(30)
 SCHEDULE_NAME              NOT NULL VARCHAR2(30)
 START_DATE                          TIMESTAMP(6) WITH TIME ZONE
 REPEAT_INTERVAL                     VARCHAR2(4000)
 END_DATE                            TIMESTAMP(6) WITH TIME ZONE
 COMMENTS                            VARCHAR2(240)

The mathematical operations and most of the date functions mentioned previously are also valid for timestamps.  In addition to the date functions, Oracle provides several timestamp specific functions listed in Table 3.6 below.

select
  systimestamp(3)
from
  dual;
SYSTIMESTAMP(3)
-----------------------------
10-JUL-04 19.09.35.793 +01:00

1 row selected.

current_timestamp(precision)

Similar to the systimestamp function, but returns the current TIMSTAMP WITH TIME ZONE within the sessions time zone to the specified precision.  If no precision is specified, the default is 6.

localtimestamp(precision)

Similar to the current_timestamp function, but returns the current TIMESTAMP with time zone within the sessions time zone to the specified precision.  If no precision is specified, the default is six.

to_timestamp(string, format)

Converts a specified string to a TIMESTAMP using the specified format mask. If the format mask is omitted, the nls_timestamp_format or nls_timestamp_tz_format value is used depending on the context.

to_timestamp_tz(string, format)

Converts a specified string to a TIMESTAMP WITH TIME ZONE using the specified format mask. If the format mask is omitted, the nls_timestamp_format or nls_timestamp_tz_format value is used depending on the context.

from_tz(timestamp, timezone)

Converts a TIMESTAMP and a string representing the time zone to a TIMESTAMP WITH TIME ZONE.

dbtimezone

Returns the database time zone.

sessiontimezone

Returns the current session's time zone.

sys_extract_utc(timestamp)

Returns the UTC, or GMT timestamp from a specified TIMESTAMP WITH TIME ZONE.

1 row selected.

extract(datepart from date)

Extracts the specified datepart from the specified timestamp.

Table 3.6 ? Oracle timestamp specific functions

In the Oracle10g scheduler, the repeat interval of a job can be defined by a PL/SQL expression or by using a new calendar syntax, which will be presented later in this chapter.

Now that the two datetime values of DATE and TIMESTAMP have been introduced, the following section will illustrate how intervals can be stored in the database and defined using the interval literal syntax.

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.