Developing Time-Oriented Applications in SQL | Richard T. Snodgrass

# DATA TYPES

## Oracle8 Server Implementation Examples

## Detailed Contents

This document outlines temporal data type support for the Oracle DBMS, as described in Chapters 2 & 3. It follows the general outline of the TDB book and is arranged in the following manner. The information provided was derived from the on-line documentation included with the Oracle DBMS. Oracle v7.3.2 was used to verify the correctness of the information provided.

> Instants and Intervals

> > Operations
> > Examples

> > > Instant Examples
> > > Interval Examples

> Periods

> > Operations
> > Examples

##

## Instants and Intervals (Oracle8 Server)

### Operations

SQL-92 | Oracle Equivalent
Types:
DATE | DATE, ignoring the hour, minute, and second fields
TIME | DATE, ignoring the century, year, month, and day fields
TIMESTAMP | DATE (to second granularity)
TIME WITH TIME ZONE | Not supported
TIMESTAMP WITH TIME ZONE | Not supported
INTERVAL YEAR TO MONTH | Not supported
INTERVAL DAY TO SECOND | NUMBER(12, 5)
Literals:
DATE '1997-01-01' | TO_DATE('1997-01-01', 'YYYY-MM-DD')
TIME '12:34:56' | TO_DATE('12:34:56', 'HH24:MI:SS')
TIMESTAMP '1997-01-01 12:34:56' | TO_DATE('1997-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS')
INTERVAL '3-4' YEAR TO MONTH | Not supported
INTERVAL '1 23:45:12' DAY TO SECOND | (TO_NUMBER(SUBSTR('1 23:45:12', 1, LENGTH('1 23:45:12') - 9)) + TO_NUMBER(SUBSTR('1 23:45:12', LENGTH('1 23:45:12') - 7, 2)) / 24 + TO_NUMBER(SUBSTR('1 23:45:12', LENGTH('1 23:45:12') - 4, 2)) / 1440 + TO_NUMBER(SUBSTR('1 23:45:12', LENGTH('1 23:45:12') - 1, 2)) / 86400) (result is a fractional Julian day)
Predicates:
d1 = d2 | d1 = d2
d1 < d2 | d1 < d2
d1 <> d2 | d1 <> d2
d1 BETWEEN d2 AND d3 | d1 BETWEEN d2 AND d3
i1 = i2 | j1 = j2
i1 < i2 | j1 < j2
i1 <> i2 | j1 <> j2
i1 BETWEEN i2 AND i3 | j1 BETWEEN j2 AND j3
d IS NULL | d IS NULL
i IS NULL | j IS NULL
(d1, i2) OVERLAPS (d3, d4) | d1 <= d4 AND d3 <= (d1 + j2)
Datetime Constructors:
d + i | d + j or ADD_MONTHS(d, n)
i + d | j + d
d - i | d - j or ADD_MONTHS(d, -n)
d AT i | Not supported
d AT LOCAL | Not supported
CURRENT_DATE | TRUNC(SYSDATE)
CURRENT_TIME | TO_DATE(TO_CHAR(SYSDATE, 'HH24:MI:SS'), 'HH24:MI:SS')
CURRENT_TIMESTAMP | SYSDATE
Internal Constructors:
i1 + i2 | j1 + j2
i1 - i2 | j1 - j2
d1 - d2 qual | d1 - d2 (result is a fractional Julian number)
d1 - d2 INTERVAL MONTH | MONTHS_BETWEEN(d1, d2) (result is a fractional number of months)
i * n | j * n
n * i | n * j
i1 / i2 | j2 / j2
+ i | + j
- i | - j
Other Operators:
CAST(d AS DATE) | TRUNC(d)
CAST(d AS TIME) | TO_DATE(TO_CHAR(d, 'HH24:MI:SS'), 'HH24:MI:SS')
CAST(d AS TIMESTAMP) (where d is a DATE) | TRUNC(d)
CAST(d AS TIMESTAMP) (where d is a TIME) | TRUNC(SYSDATE) + (d - TRUNC(d))
CAST(i AS INTERVAL YEAR TO MONTH) | Not supported
CAST(i AS INTERVAL DAY TO SECOND) | j
CAST(d AS CHAR) | TO_CHAR(d, 'YYYY-MM-DD HH24:MI:SS')
CAST(i AS DATE) | TRUNC(j, 0) || ' ' || TO_CHAR(j + TO_DATE(1, 'J'), 'HH24:MI:SS')
CAST(i AS INTEGER) (where i is a DAY) | TRUNC(j, 0)
CAST(i AS INTEGER) (where i is a HOUR) | TRUNC(j * 24, 0)
CAST(i AS INTEGER) (where i is a MINUTE) | TRUNC(j * 1440, 0)
CAST(i AS INTEGER) (where i is a SECOND) | TRUNC(j * 86400, 0)
EXTRACT(DAY from d) | TRUNC(d, 'DD') - TRUNC(d, 'MM') + 1
EXTRACT(DAY from i) | TRUNC(j, 0)
EXTRACT(HOUR from i) | TRUNC(j * 24, 0) - (TRUNC(j, 0) * 24)
Operators not in SQL-92:
Convert d to Julian day | TO_CHAR(d, 'J')
Convert Julian day n to DATE | n + TO_DATE(1, 'J')
Pick the earliest date | LEAST(d1, ..., dn)
Pick the latest date | GREATEST(d1, ..., dn)
Pick the last day of the month | LAST_DAY(d)
Get the next day of the week | NEXT_DAY(d, string)

### Examples

The instant and interval test cases fully implement queries utilizing all of the expressions from the preceding table. This SQL file can be executed via Oracle's SQL*Plus program. Simply type "start InstantAndIntervalTestCases.sql" from the SQL*Plus command line. The actual instant and interval values used in the queries are defined at the top of the source file. These values can be modified to change the outcome of the various queries.

> Instant Examples

> This example demonstrates the use of instants in the Oracle DBMS. The following is the schema for the table used in the example. The People table contains names of people and their birthdates.

> Name VARCHAR(20) | BirthDate DATE

> Assume the People table contains the following rows:

> Name | BirthDate
> Chris | TO_DATE('1976-09-23', 'YYYY-MM-DD')
> Nathan | TO_DATE('1980-11-01', 'YYYY-MM-DD')
> Patrick | TO_DATE('1983-04-21', 'YYYY-MM-DD')
> Emily | TO_DATE('1987-06-17', 'YYYY-MM-DD')

> The following query lists everyone with birthdates between January 1, 1980 and December 31, 1985:

> SELECT Name, BirthDate FROM People

> > WHERE BirthDate BETWEEN TO_DATE('1980-01-01', 'YYYY-MM-DD')

> > AND TO_DATE('1985-12-31', 'YYYY-MM-DD');

> Here are the results:

> Name | BirthDate
> Nathan | 1980-11-01
> Patrick | 1983-04-21

> The following query lists everyone with birthdates before September 1, 1981:

> SELECT Name, BirthDate FROM People

> > WHERE BirthDate <to_date('1981-09-01', 'yyyy-mm-dd');

> Here are the results:

> Name | BirthDate
> Chris | 1976-09-23
> Nathan | 1980-11-01

> Interval Examples

> This example demonstrates the use of intervals in the Oracle DBMS. The following is the schema for the table used in the example. The Jobs table contains job names and the estimated time for production of each.

> Name VARCHAR(20) | EstTime NUMBER(12, 5)

> Assume the Jobs table contains the following rows:

> Name | EstTime
> Green Widgets | TO_NUMBER(SUBSTR('0 14:26:32', 1, LENGTH('0 14:26:32') - 9)) + TO_NUMBER(SUBSTR('0 14:26:32', LENGTH('0 14:26:32') - 7, 2)) / 24 + TO_NUMBER(SUBSTR('0 14:26:32', LENGTH('0 14:26:32') - 4, 2)) / 1440 + TO_NUMBER(SUBSTR('0 14:26:32', LENGTH('0 14:26:32') - 1, 2)) / 86400
> Blue Widgets | TO_NUMBER(SUBSTR('0 22:52:06', 1, LENGTH('0 22:52:06') - 9)) + TO_NUMBER(SUBSTR('0 22:52:06', LENGTH('0 22:52:06') - 7, 2)) / 24 + TO_NUMBER(SUBSTR('0 22:52:06', LENGTH('0 22:52:06') - 4, 2)) / 1440 + TO_NUMBER(SUBSTR('0 22:52:06', LENGTH('0 22:52:06') - 1, 2)) / 86400
> Red Widgets | TO_NUMBER(SUBSTR('1 04:45:52', 1, LENGTH('1 04:45:52') - 9)) + TO_NUMBER(SUBSTR('1 04:45:52', LENGTH('1 04:45:52') - 7, 2)) / 24 + TO_NUMBER(SUBSTR('1 04:45:52', LENGTH('1 04:45:52') - 4, 2)) / 1440 + TO_NUMBER(SUBSTR('1 04:45:52', LENGTH('1 04:45:52') - 1, 2)) / 86400
> Yellow Widgets | TO_NUMBER(SUBSTR('2 23:30:29', 1, LENGTH('2 23:30:29') - 9)) + TO_NUMBER(SUBSTR('2 23:30:29', LENGTH('2 23:30:29') - 7, 2)) / 24 + TO_NUMBER(SUBSTR('2 23:30:29', LENGTH('2 23:30:29') - 4, 2)) / 1440 + TO_NUMBER(SUBSTR('2 23:30:29', LENGTH('2 23:30:29') - 1, 2)) / 86400

> The following query lists jobs which will take more than 1 day to complete:

> SELECT Name, EstTime FROM Jobs

> > WHERE EstTime > TO_NUMBER(SUBSTR('1 00:00:00', 1, LENGTH('1 00:00:00') - 9))

> > + TO_NUMBER(SUBSTR('1 00:00:00', LENGTH('1 00:00:00') - 7, 2)) / 24

> > + TO_NUMBER(SUBSTR('1 00:00:00', LENGTH('1 00:00:00') - 4, 2)) / 1440

> > + TO_NUMBER(SUBSTR('1 00:00:00', LENGTH('1 00:00:00') - 1, 2)) / 86400;

> Here are the results:

> Name | EstTime
> Red Widgets | 1.19852
> Yellow Widgets | 2.9795

> The following query lists jobs which will take between 6 and 18 hours to complete:

> SELECT Name, EstTime FROM Jobs WHERE EstTime

> > BETWEEN TO_NUMBER(SUBSTR('0 06:00:00', 1, LENGTH('0 06:00:00') - 9))

> > + TO_NUMBER(SUBSTR('0 06:00:00', LENGTH('0 06:00:00') - 7, 2)) / 24

> > + TO_NUMBER(SUBSTR('0 06:00:00', LENGTH('0 06:00:00') - 4, 2)) / 1440

> > + TO_NUMBER(SUBSTR('0 06:00:00', LENGTH('0 06:00:00') - 1, 2)) / 86400

> > AND TO_NUMBER(SUBSTR('0 18:00:00', 1, LENGTH('0 18:00:00') - 9))

> > + TO_NUMBER(SUBSTR('0 18:00:00', LENGTH('0 18:00:00') - 7, 2)) / 24

> > + TO_NUMBER(SUBSTR('0 18:00:00', LENGTH('0 18:00:00') - 4, 2)) / 1440

> > + TO_NUMBER(SUBSTR('0 18:00:00', LENGTH('0 18:00:00') - 1, 2)) / 86400;

> Here are the results:

> Name | EstTime
> Green Widgets | 0.60176

##

## Periods (Oracle8 Server)

### Operations

SQL-92 | Oracle Equivalent
Types:
period | [DATE, DATE)
Predicates:
p equals q | p1 = q1 AND p2 = q2
p before q | p2 < q1
p before-1 q | q2 < p1
p meets q | p2 = q1
p meets-1 q | q2 = p1
p overlaps q | p1 < q1 AND q1 < p2
p overlaps-1 q | q1 < p1 AND p1 < q2
p during q | q1 < p1 AND p2 < q2
p during-1 q | p1 < q1 AND q2 < p2
p starts q | p1 = q1 AND p2 < q2
p starts-1 q | q1 = p1 AND q2 < p2
p finishes q | q1 < p1 AND p2 = q2
p finishes-1 q | p1 < q1 AND q2 = p2
p OVERLAPS q | p1 <= q1 AND q1 <= p2
p IS NULL | p1 IS NULL
Datetime Constructors:
beginning(p) | p1
previous(p) | p1 - 1
last(p) | p2 - 1
ending(p) | p2
Interval Constructors:
duration(p) | p2 - p1
extract_time_zone(p) | Not supported
Period Constructors:
p + i | [p1 + i, p2 + i)
i + p | [p1 + i, p2 + i)
p - i | [p1 - i, p2 - i)
a extend b | [LEAST(a, b), GREATEST(a, b))
p extend q | [LEAST(p1, q1), GREATEST(p2, q2))
p extend a | [LEAST(p1, a), GREATEST(p2, a))
a extend p | [LEAST(a, p1), GREATEST(a, p2))
p INTERSECT q | [GREATEST(p1, q1), LEAST(p2, q2))
p - q | Not supported
p UNION q | [LEAST(p1, q1), GREATEST(p2, q2))
p AT TIME ZONE i | Not supported
p AT LOCAL | Not supported
Other Operators:
CAST(a AS PERIOD) | [a, a)
CAST(p AS CHAR) | '[' || TO_CHAR(p1, 'YYYY-MM-DD HH24:MI:SS') || ', ' || TO_CHAR(p2, 'YYYY-MM-DD HH24:MI:SS') || ')'

### Examples

The period test cases fully implement queries utilizing all of the expressions from the preceding table. This SQL file can be executed via Oracle's SQL*Plus program. Simply type "start PeriodrvalTestCases.sql" from the SQL*Plus command line. The actual instant and interval values used in the queries are defined at the top of the source file. These values can be modified to change the outcome of the various queries.

This example demonstrates the use of periods in the Oracle DBMS. The following is the schema for the table used in the example. The Employee table contains names of employees, pay rates, and the period for which the pay rate was valid.

Name VARCHAR(20) | PayRate NUMBER(5,2) | StartDate DATE | EndDate DATE

Assume the Employee table contains the following rows:

Name | PayRate | StartDate | EndDate
Chris | 6.00 | TO_DATE('1994-05-15', 'YYYY-MM-DD') | TO_DATE('1996-06-04', 'YYYY-MM-DD')
Chris | 18.00 | TO_DATE('1996-06-04', 'YYYY-MM-DD') | TO_DATE('3000-01-01', 'YYYY-MM-DD')
John | 28.00 | TO_DATE('1991-03-18', 'YYYY-MM-DD') | TO_DATE('1993-03-25', 'YYYY-MM-DD')
John | 32.00 | TO_DATE('1993-03-25', 'YYYY-MM-DD') | TO_DATE('1995-04-10', 'YYYY-MM-DD')
John | 35.00 | TO_DATE('1995-04-10', 'YYYY-MM-DD') | TO_DATE('3000-01-01', 'YYYY-MM-DD')

The following query lists everyone's pay rate as of June 1, 1995:

SELECT Name, PayRate FROM Employee

> WHERE TO_DATE('1995-06-01', 'YYYY-MM-DD')

> BETWEEN StartDate AND EndDate;

Here are the results:

Name | PayRate
Chris | 6.00
John | 35.00

The following query lists everyone's whose pay rate changed between January 1, 1995 and December 31, 1995:

SELECT DISTINCT Name FROM Employee

> WHERE TO_DATE('1995-01-01', 'YYYY-MM-DD') <startdate

> AND StartDate <to_date('1995-12-31', 'yyyy-mm-dd');

Here are the results:

Name
John

#### HTML Credits:

Christopher Cooper, Department of Computer Science, University of Arizona (ccooper@cs.arizona.edu)
Rachana R. Shah, Department of Computer Science, University of Arizona (rachana@cs.arizona.edu)
Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
April 28, 1999(Last Update)

#### Oracle-Specific Code Credits:

Christopher Cooper, Department of Computer Science, University of Arizona (ccooper@cs.arizona.edu)
December 17, 1997 (Last Update)