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

# DATA TYPES

## Ingres Implementation Examples

#

## Contents

This document summarizes temporal data type support from Ingres 5.0. It follows the general outline of the TDB book and is arranged in the following manner:

> Instants and Intervals in Ingres

> > Instants
> > Intervals
> > Predicates
> > Temporal Constructors
> > Other Operations
> > SQL-92 vs Ingres at a Glance

> Periods in Ingres
> Query Examples
> References

##

## Instants and Intervals in Ingres

Like Oracle, Ingres also supplies only one temporal type, `DATE`. But unlike Oracle, it can also be used to designate an interval. Therefore, in Ingres `DATE` is actually a universal temporal data type, and can hold either a date, like '12/03/1997', or a time, like '21:15:55', or a time with time zone, like '21:15:55 MST', or a timestamp, like '12/03/1997 21:15:55', or a timestamp with time zone, like '12/03/1997 21:15:55 MST', or a date interval, like '2 years 3 months 4 days', or a time interval, like '5 hours 6 minutes 7 seconds'. A `DATE` value in Ingres always occupies 12 bytes of storage, though its constants and output are always written as up-to-25-character strings of certain format, depending on the setting of `II_DATE_FORMAT`.

### Instants

Ingres second. Consequently, it is impossible `DATE` data type can only handle dates ranging from 1-Jan-1582 to 31-Dec-2382 with a resolution of 1 to represent instants with a finer precision than second in Ingres. Depending on the setting of `II_DATE_FORMAT`, date literals can be written in a variety formats. For example, if `II_DATE_FORMAT = US`, for today's date, December 3, 1997, the following are all valid:

> `'12/03/1997'
> '12-03-1997'`
> `'120397'`
> `'03-Dec-1997'
> '1997.12.03'`
> `'1997_12_03'`
> `'12-03'`
> `'12/03'`
> `'today'`

As usual, the current year is assumed if it is omitted; or the current century is assumed if only two digits are provided as the year value, unless `II_DATE_CENTURY_BOUNDARY` is set.

But times have fewer formats, and independent the setting of `II_DATE_FORMAT`:

    	'hh:mm[:ss][am | pm][zzz]'

where `:00` is assumed if `:`ss is omitted; the time is assumed to be on a 24-hour clock if both `am` and `pm` are omitted; and the local time zone is assumed if zzz is omitted. Ingres only supports the following time zones:

    	edt	cdt	mdt	pdt	gmt	est	cst	mst	pst

    To form a timestamp in Ingres, just concatenate a valid date with a valid time.

For example, a part supplier has a table called "delivery", with columns called "customer", "orderDate", "shipDate", and "recvDate". The query how many orders were shipped more than 5 days after they were placed can be written in SQL as follows:

        SELECT x = count(customer)
        FROM delivery
        WHERE orderDate <(shipdate '5 days');

### Intervals

As mentioned above, Ingres `DATE` data type can also hold an interval value, but also with a limited range from -800 years to +800 years, and also with a resolution of 1 second. Valid interval `qual`s are as follows:

    	yrs	years	mos	months	days
    	hrs	hours	mins	minutes	sec	seconds

Therefore, most of SQL-92 year-month intervals can be simulated in Ingres without difficulty, but only part of SQL-92 day-second intervals can be simulated in Ingres.

For example, using the same table described above, the query how many days company XYZ waited from the time it placed its order until it received the order can be written in SQL as follows:

        SELECT x = (recvDate - orderDate)
        FROM delivery
        WHERE customer = 'XYZ';

### Temporal Predicates

Temporal predicts on `DATE`s supported in Ingres includes all comparison operators: ``=`', `<>', `<`', `<=`', `>', ``>=`', and ``BETWEEN`', based on the obvious chronological ordering. For `not equals' operator, it can also be written as ``!=`' or ``^=`'.

### Temporal Constructors

Ingres `CHAR` and `VCHAR` string literals conforming to the above-described valid `DATE` formats can be assigned directly to `DATE`s or converted explicitly to `DATE` values by invoking function `date`. In addition, a date value can also be constructed by adding/subtracting an interval value to/from another date value and an interval value can be constructed by adding/subtracting an interval value to/from another interval value or subtracting a date value from another date value, all using ordinary arrhythmic ``+`'/``-`' operator.

Other Operations

Ingres `DATE` values can be assigned directly to `CHAR` or `VCHAR` strings. In addition, Ingres also provides other functions on `DATE` values:

`dow(``d``)`
  gives the day of the week for the provided data value `d`.
`date_trunc(``unit``,` `date``)`
  returns the first date within a specified `unit` of measurement. It is very useful for grouping date values.
`date_part(``qual``,` `date``)`
  returns an integer that is the part specified by the `qual` of the given date.
`interval(``qual``,` `i``)`
  returns the number of time units specified by the `qual` contained within the interval `i`.

### SQL-92 vs Ingres at a Glance

The following table shows Ingres' equivalents(if exist) to SQL-92 instants and intervals and how operations can be simulated in Ingres. In the SQL-92 column, qual denotes an SQL-92 qualifier. In the Ingres column, qual' denotes an Ingres qualifier. In both columns, i, i1, i2, and i3 denote interval values, d, d1, d2,d3, and d4 denote datetime values.

SQL-92 | Ingres Equivalent
--- | ---
Types:
`DATE` | `DATE`, in absolute date format, ignoring the hour, minute, second, and time zone fields
`TIME` | `DATE`, in absolute date format, ignoring the year, month, day, and time zone fields
`TIMESTAMP` | `DATE`, in absolute date format, ignoring the time zone field(to second granularity)
`TIME WITH TIME ZONE` | `DATE`, in absolute date format, ignoring the year, month, day fields
`TIMESTAMP WITH TIME ZONE` | `DATE`, in full absolute date format
`INTERVAL YEAR TO MONTH` | `DATE`, in date interval format and ignoring day field
`INTERVAL DAY TO SECOND` | `DATE`, in time interval format to second granularity
Literals:
`DATE '1997-01-01'` | depends on the setting of `II_DATE_FORMAT`. For example, `date('1997_01_01')` when `II_DATE_FORMAT = US` and `date('1997-01-01')` when `II_DATE_FORMAT = SWEDEN/FINLAND`
`TIME '12:34:56'` | `date('12:34:56')`
`TIMESTAMP '1997-01-01 12:34:56'` | `date('1997-01-01 12:34:56')`, but as mentioned above, the format of the date part actually depends on the setting of `II_DATE_FORMAT`
`INTERVAL '3-4' YEAR TO MONTH` | `date('3 years 4 months')`
`INTERVAL '9 23:45:12' DAY TO SECOND` | `date('9 days 23 hours 45 minutes 12 seconds')`
Predicates:
d1 `=` d2 | d1 `=` d2
d1 <`` `d``2` | `d``1``<` `d``2`
`d``1``<>` d2 | d1 <>`` d2
d1 `BETWEEN` d2 `AND` d3 | d1 `BETWEEN` d2 `AND` d3
i1 `=` i2 | i1 `=` i2
i1 <`` `i``2` | `i``1``<` `i``2`
`i``1``<>` i2 | i1 <>`` i2
i1 `BETWEEN` i2 `AND` i3 | i1 `BETWEEN` i2 `AND` i3
d `IS NULL` | d `IS NULL`
i `IS NULL` | i `IS NULL`
(d1, i2) `OVERLAPS` (d3, d4) | d1 <= `d``4``AND` d3 <= `(``d``1``+` i2)
Datetime Constructors:
d`+` i | d`+` i
i`+` d | i`+` d
d`-` i | d`-` i
d `AT` i | Not supported
d `AT LOCAL` | Not supported
`CURRENT_DATE` | `'today'`
`CURRENT_TIME` | `'now'`, ignoring the year, month, and day fields
`CURRENT_TIMESTAMP` | `'now'`
Interval Constructors:
i1`+` i2 | i1`+` i2
i1`-` i2 | i1`-` i2
d1`-` d2 qual | `date(concat(ascii(interval(`qual'`,` d1 `-` d2`)),` qual'`))`
d1`-` d2 `INTERVAL MONTH` | `date(concat(ascii(interval('months',` d1 `-` d2`)), 'months'))`
i `*` n | `date(concat(ascii(interval(`qual'`,` i`) *` n`),` qual'`))`
n `*` i | `date(concat(ascii(interval(`qual'`,` i`) *` n`),` qual'`))`
i `/` n | `date(concat(ascii(int4(interval(`qual'`,` i`) /` n`)),` qual'`))`
i1 `/` i2 | `int4(interval('seconds',` i1`) / interval('seconds',` i2`))`
`+` i | `+` i
`-` i | `-` i
Other Operators:
`CAST(`d `AS DATE)` | `date_trunc('days',` d`)`
`CAST(`d `AS TIME)` | No direct support. But can be assigned to a string, extract the time part, and then convert back to `DATE` data type
`CAST(`d `AS TIMESTAMP)` (where d is a `DATE`) | `date(concat(`d`, '00:00:00'))`
`CAST(`d `AS TIMESTAMP)` (where d is a `TIME`) | `date(concat('today',` d`))`
`CAST(`i `AS INTERVAL YEAR TO MONTH)` | `date(concat(ascii(interval('years',` i`)), 'years')) + date(concat(ascii(interval('months',` i`) - 12 * interval('years',` i`)), 'months'))`
`CAST(`i `AS INTERVAL DAY TO SECOND)` | `date(concat(ascii(interval('hours',` i`)), 'hours')) + (`i`- date(concat(ascii(interval('hours',` i`)))))`
`CAST(`d `AS CHAR)` | d can be assigned to a `CHAR` string directly
`CAST(`i `AS CHAR)` | i can be assigned to a `CHAR` string directly
`CAST(`i `AS INTEGER)`(where i is a `DAY`) | `interval('days',` i`)`
`CAST(`i `AS INTEGER)`(where i is a `HOUR`) | `interval('hours',` i`)`
`CAST(`i `AS INTEGER)`(where i is a `MINUTE`) | `interval('minutes',` i`)`
`CAST(`i `AS INTEGER)`(where i is a `SECOND`) | `interval('seconds',` i`)`
`EXTRACT(DAY FROM` d`)` | `date_part('day',` d`)`
`EXTRACT(DAY FROM` i`)`(where i is a `DAY TO SECOND`) | `interval('days',` i`)`
`EXTRACT(HOUR FROM` i`)` (where i is a `DAY TO SECOND`) | `interval('hours',` i`) - interval('days',` i`) * 24`
Operators not in SQL-92:
pick the day of the week for the date d | `dow(`d`)`
pick the week for the date d | `date_part('week',` d`)`
pick the first date of the week d | `date_trunc('weeks',` d`)`
pick the first date of the month d | `date_trunc('months',` d`)`
pick the last date of the month d | `date_trunc('months', (`d`+ '1 month')) - '1 day'`

## Periods in Ingres

Like other commercial DBMS's, Ingres doesn't directly support the period data type. However, most of the operations on periods defined in SQL-92 can be simulated in Ingres relatively easily. The following table shows how the period operations can be implemented in Ingres, using the closed-open representation of a pair of Ingres `DATE`s. In the SQL-92 column, a and b denote datetime values, p and q denote period values, and i denotes an interval value. In the Ingres column, p1 and p2 denote the two components of the representation of the period p, q1 and q2 denote the two components of the representation of the period q, a and b denote Ingres `DATE` values, i denotes an Ingres interval value.

SQL-92 | Ingres Equivalent
--- | ---
Types:
`period` | `[DATE, DATE)`
Predicates:
p `equals` q | p1 `=` q1 `AND` p2 `=` q2
p `before` q | p2 <`` `q``1`
`p before``-1` q | q2 <`` `p``1`
`p meets` q | p2 `=` q1
p `meets``-1` q | q2 `=` p1
p `overlaps` q | p1 <= `q``1``AND` q1 <`` `p``2```
`p overlaps``-1` q | q1 <= `p``1``AND` p1 <`` `q``2`
`p during` q | q1 <`` `p``1``AND` p2 <`` `q``2`
`p during``-1` q | p1 <`` `q``1``AND` q2 <`` `p``2`
`p starts` q | p1 `=` q1 `AND` p2 <`` `q``2`
`p starts``-1` q | q1 `=` p1 `AND` q2 <`` `p``2`
`p finishes` q | q1 <`` `p``1``AND` p2 `=` q2
p `finishes``-1` q | p1 <`` `q``1``AND` q2 `=` p2
p `OVERLAPS` q | p1 <`` `q``2``AND` q1 <`` `p``2```
`p` `IS NULL` | p1 `IS NULL`
Datetime Constructors:
`beginning``(`p`)` | p1
`previous``(`p`)` | p1`- '1 day'`(where `day` should actually be the qualifier corresponding to the granularity of the period)
`last``(`p`)` | p2`- '1 day'`(where `day` should actually be the qualifier corresponding to the granularity of the period)
`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 | No direct support
p `extend` q | No direct support
p `extend` a | No direct support
a `extend` p | No direct support
p `INTERSECT` q | No direct support
p`-` q | No direct support
p `UNION` q | No direct support
p `AT TIME ZONE` i | Not supported
p `AT LOCAL` | Not supported
Other Operators:
`CAST(`a `AS PERIOD)` | `[`a`,` a`+ '1 day')`(where `day` should actually be the qualifier corresponding to the granularity of a)
`CAST(`p `AS CHAR)` | `concat(concat(concat(concat('[',` p1`), ','),` p2`), ')')`

##

##

## Query Examples

Suppose a database has a table `Employee` with a column `BirthDate`. In Ingres, the query for information on those born on 2 January 1970 can be written as either of the following:

SELECT *
FROM Employee
WHERE BirthDate = date('1970.01.02')

    	SELECT *
    	FROM Employee
    	WHERE NOT BirthDate <> (date('1967.08.30') + date('2 yrs 4 mos 3 days'))

    	SELECT *
    	FROM Employee
    	WHERE (BirthDate - date('11 hrs 30 mins')) = date('1970.01.01 12:30')

    	SELECT *
    	FROM Employee
    	WHERE (BirthDate - date_trunc('months', BirthDate) = date('24 hrs')

    	SELECT *
    	FROM Employee
    	WHERE BirthDate BETWEEN date('1970.01.02') AND date('1970.01.02')

    	SELECT *
    	FROM Employee
    	WHERE date_part('year', BirthDate) = 1970
    	  AND date_part('month', BirthDate) = 1
    	  AND date_part('day', BirthDate) = interval('days', '2 days');

##

## References

1. INGRES Technical Notes #8 for Release 6 UNIX, Mar 1990.
2. Ingres Corporation, INGRES/OpenSQL Reference Manual for UNIX and VMS, Release 6.4, Feb 1992.
3. Ingres Corporation, Introducing INGRES for UNIX and VMS, Release 6.4, Dec 1991.
4. Date, C. J., A Guide to INGRES, Addison-Wesley, 1987.
5. Hann, Roy, Ingres Frequently Asked Questions, Version 1.8, 1997. Also available at website `http://www.naiua.org/faqs.html`
6. `newsgroup:` `comp.database.ingres`

##

#### HTML and Text Credits:

Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
April 8, 1999(Last Update)