TimesTen Intervals

This section includes the following topics:

• Using Interval Data Types

• Using DATE and TIME Data Types

• Handling Time Zone Conversions

• Datetime and Interval Data Types in Arithmetic Operations

Using Interval Data Types

TimesTen supports interval types only in a constant specification or intermediate expression result. Interval types cannot be the final result. Columns cannot be defined with an interval type. See "Type Specifications".

You can specify a single-field literal that is an interval in an expression, but you cannot specify a complete expression that returns an interval data type. Instead, the EXTRACT function must be used to extract the desired component of the interval result.

TimesTen supports interval literals of the following form:

INTERVAL [+/-] CharString IntervalQualifier

Using DATE and TIME Data Types

This section shows some DATE, TIME, and TIMESTAMP data type examples:

To create a table named sample that contains a column dcol of type DATE and a column tcol of type TIME, use the following:

CREATE TABLE sample (tcol TIME, dcol DATE);

To insert DATE and TIME values into the sample table, use this:

INSERT INTO sample VALUES (TIME '12:00:00', DATE '1998-10-28');

To select all rows in the sample table that are between noon and 4:00 p.m. on October 29, 1998, use the following:

SELECT * FROM sample WHERE dcol = DATE '1998-10-29' 
AND tcol BETWEEN TIME '12:00:00' AND TIME '16:00:00';

To create a table named sample2 that contains a column tscol of type TIMESTAMP and then select all rows in the table that are between noon and 4:00 p.m. on October 29, 1998, use these statements:

CREATE TABLE sample2 (tscol TIMESTAMP);
INSERT INTO sample2 VALUES (TIMESTAMP '1998-10-28 12:00:00');
SELECT * FROM sample2 WHERE tscol
BETWEEN TIMESTAMP '1998-10-29 12:00:00' AND '1998-10-29 16:00:00';

Note:

TimesTen enables both literal and string formats of the TIME, DATE, and TIMESTAMP types. For example, timestring ('12:00:00') and timeliteral (TIME '16:00:00') are both valid ways to specify a TIME value. TimesTen reads the first value as CHAR type and later converts it to TIME type as needed. TimesTen reads the second value as TIME. The examples above use the literal format. Any values for the fraction not specified in full microseconds result in a "Data truncated" error.

Handling Time Zone Conversions

TimesTen does not support TIMEZONE. TIME and TIMESTAMP data type values are stored without making any adjustment for time difference. Applications must assume one time zone and convert TIME and TIMESTAMP to that time zone before sending values to the database. For example, an application can assume its time zone to be Pacific Standard Time. If the application is using TIME and TIMESTAMP values from Pacific Daylight Time or Eastern Standard Time, for example, the application must convert TIME and TIMESTAMP to Pacific Standard Time.

Datetime and Interval Data Types in Arithmetic Operations

You can perform numeric operations on date, timestamp and interval data. TimesTen calculates the results based on the rules:

• You can add or subtract a numeric value to or from a DATE or TIMESTAMP value. TimesTen internally converts TIMESTAMP values to DATE values.

• You can add or subtract a numeric value to or from a TT_DATE or TT_TIMESTAMP value and the resulting value is TT_DATE or TT_TIMESTAMP respectively.

• Numeric values are treated as number of days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago.

• Subtracting two date columns results in the number of days between the two dates. The return type is numeric.

• You cannot add date values. You cannot multiple or divide date or timestamp values.

Table 1-4 is a matrix of datetime arithmetic operations.

Table 1-4 DateTime arithmetic operations

Blank	DATE	TT_DATE	TIMESTAMP	TT_TIMESTAMP	NUMERIC	INTERVAL
DATE	not applicable	not applicable	not applicable	not applicable	not applicable	not applicable
+ (plus)	unsupported	unsupported	unsupported	unsupported	DATE	DATE
- (minus)	NUMBER	NUMBER	INTERVAL	INTERVAL	DATE	DATE
* (multiply)	unsupported	unsupported	unsupported	unsupported	unsupported	unsupported
/ (divide)	unsupported	unsupported	unsupported	unsupported	unsupported	unsupported
TT_DATE	not applicable	not applicable	not applicable	not applicable	not applicable	not applicable
+ (plus)	unsupported	unsupported	unsupported	unsupported	TT_DATE	TT_DATE
- (minus)	NUMBER	TT_BIGINT	INTERVAL	INTERVAL	TT_DATE	TT_DATE
* (multiply)	unsupported	unsupported	unsupported	unsupported	unsupported	unsupported
/ (divide)	unsupported	unsupported	unsupported	unsupported	unsupported	unsupported
TIMESTAMP	not applicable	not applicable	not applicable	not applicable	not applicable	not applicable
+ (plus)	unsupported	unsupported	unsupported	unsupported	DATE	TIMESTAMP
- (minus)	INTERVAL	INTERVAL	INTERVAL	INTERVAL	DATE	TIMESTAMP
* (multiply)	unsupported	unsupported	unsupported	unsupported	unsupported	unsupported
/ (divide)	unsupported	unsupported	unsupported	unsupported	unsupported	unsupported
TT_TIMESTAMP	not applicable	not applicable	not applicable	not applicable	not applicable	not applicable
+ (plus)	unsupported	unsupported	unsupported	unsupported	TT_TIMESTAMP	TT_TIMESTAMP
- (minus)	INTERVAL	INTERVAL	INTERVAL	INTERVAL	TT_TIMESTAMP	TT_TIMESTAMP
* (multiply)	unsupported	unsupported	unsupported	unsupported	unsupported	unsupported
/ (divide)	unsupported	unsupported	unsupported	unsupported	unsupported	unsupported
NUMERIC	not applicable	not applicable	not applicable	not applicable	not applicable	not applicable
+ (plus)	DATE	TT_DATE	DATE	TT_TIMESTAMP	Not applicable	unsupported
- (minus)	unsupported	unsupported	unsupported	unsupported	Not applicable	unsupported
* (multiply)	unsupported	unsupported	unsupported	unsupported	Not applicable	INTERVAL
/ (divide)	unsupported	unsupported	unsupported	unsupported	Not applicable	unsupported
INTERVAL	Not applicable	Not applicable	Not applicable	Not applicable	Not applicable	Not applicable
+ (plus)	DATE	TT_DATE	TIMESTAMP	TT_TIMESTAMP	unsupported	INTERVAL
- (minus)	unsupported	unsupported	unsupported	unsupported	unsupported	INTERVAL
* (multiply)	unsupported	unsupported	unsupported	unsupported	INTERVAL	unsupported
/ (divide)	unsupported	unsupported	unsupported	unsupported	INTERVAL	unsupported

Note:

An interval data type cannot be the final result of a complete expression. The EXTRACT function must be used to extract the desired component of this interval result.

SELECT tt_date1 - tt_date2 FROM t1;
SELECT EXTRACT(DAY FROM timestamp1-timestamp2) FROM t1;
SELECT * FROM t1 WHERE timestamp1 - timestamp2 = NUMTODSINTERVAL(10, 'DAY');
SELECT SYSDATE + NUMTODSINTERVAL(20,'SECOND') FROM dual;
SELECT EXTRACT (SECOND FROM timestamp1-timestamp2) FROM dual;
/* select the microsecond difference between two timestamp values d1 and d2 */
SELECT 1000000*(EXTRACT(DAY FROM d1-d2)*24*3600+
EXTRACT(HOUR FROM d1-d2)*3600+
EXTRACT(MINUTE FROM d1-d2)*60+EXTRACT(SECOND FROM d1-d2) FROM d1;

This example inserts TIMESTAMP values into two columns and then subtracts the two values using the EXTRACT function:

Command> CREATE TABLE ts (id TIMESTAMP, id2 TIMESTAMP);
Command> INSERT INTO ts VALUES (TIMESTAMP '2007-01-20 12:45:23', 
                                TIMESTAMP '2006-12-25 17:34:22');
1 row inserted.
Command> SELECT EXTRACT (DAY FROM id - id2) FROM ts;
< 25 >
1 row found.

The following queries return errors. You cannot select an interval result:

SELECT timestamp1 - timestamp2 FROM t1;

You cannot compare an INTERVAL YEAR TO MONTH with an INTERVAL DAY TO SECOND:

SELECT * FROM t1 WHERE timestamp1 - timestamp2 = NUMTOYMINTERVAL(10, 'YEAR');

You cannot compare an INTERVAL DAY TO SECOND with an INTERVAL DAY:

SELECT * FROM t1 WHERE timestamp1 - timestamp2 = INTERVAL '10' DAY;

You cannot extract YEAR from an INTERVAL DAY TO SECOND:

SELECT EXTRACT (YEAR FROM timestamp1 - timestamp2) FROM dual;

Restrictions on Datetime and Interval Arithmetic Operations

Consider these restrictions when performing datetime and interval arithmetic:

• The results for addition and subtraction with DATE and TIMESTAMP types for INTERVAL YEAR and INTERVAL MONTH are not closed. For example, adding one year to the DATE or TIMESTAMP of '2004-02-29' results in a date arithmetic error (TimesTen error 2787) because February 29, 2005 does not exist (2005 is not a leap year). Adding INTERVAL '1' month to DATE '2005-01-30' also results in the same error because February never has 30 days.

• The results are closed for INTERVAL DAY.

• An interval data type cannot be the final result of a complete expression. The EXTRACT function must be used to extract the desired component of the interval result.