1 Data Types

CHAR

The CHAR type specifies a fixed length character string. If you insert a value into a CHAR column and the value is shorter than the defined column length, then TimesTen blank-pads the value to the column length. If you insert a value into a CHAR column and the value is longer than the defined length, TimesTen returns an error.

By default, the column length is defined in bytes. Use the CHAR qualifier to define the column length in characters. The size of a character ranges from one byte to four bytes depending on the database character set. The BYTE and CHAR qualifiers override the NLS_LENGTH_SEMANTICS parameter setting. For more information about NLS_LENGTH_SEMANTICS, see "ALTER SESSION" and "Setting globalization support attributes" in Oracle TimesTen In-Memory Database Operations Guide.

The following example creates a table. Columns are defined with type CHAR and TT_CHAR. Blank padded comparison semantics are used for these types.

Command> CREATE TABLE typedemo (name CHAR (20), nnme2 TT_CHAR (20));
Command> INSERT INTO typedemo VALUES ('SMITH     ','SMITH     ');
1 row inserted.
Command> DESCRIBE typedemo;
Table USER.TYPEDEMO:
  Columns:
    NAME                            CHAR (20)
    NAME2                           TT_CHAR (20)
1 table found.
(primary key columns are indicated with *)
Command> SELECT * FROM typedemo;
< SMITH     , SMITH      >
1 row found.
Command> # Expect 1 row found; blank-padded comparison semantics
Command> SELECT * FROM typedemo WHERE name = 'SMITH';
< SMITH     , SMITH      >
1 row found.
Command> SELECT * FROM typedemo WHERE name2 = 'SMITH';
< SMITH     , SMITH      >
1 row found.
Command> # Expect 0 rows; blank padded comparison semantics.
Command> SELECT * FROM typedemo WHERE name > 'SMITH';
0 rows found.
Command> SELECT * FROM typedemo WHERE name2 > 'SMITH';
0 rows found.

The following example alters table typedemo adding column name3. The column name3 is defined with character semantics.

Command> ALTER TABLE typedemo ADD COLUMN name3 CHAR (10 CHAR);
Command> DESCRIBE typedemo;
Table USER.TYPEDEMO:
  Columns:
    NAME                            CHAR (20)
    NAME2                           TT_CHAR (20)
    NAME3                           CHAR (10 CHAR)
1 table found.

NCHAR

The NCHAR data type is a fixed length string of two-byte Unicode characters. NCHAR data types are padded to the specified length with the Unicode space character U+0020 SPACE. Blank-padded comparison semantics are used.

The following example alters table typedemo, adding column Name4. Data type is NCHAR.

Command> ALTER TABLE typedemo ADD COLUMN Name4 NCHAR (10);
Command> DESCRIBE typedemo;

Table USER.TYPEDEMO:
  Columns:
    NAME                            CHAR (20)
    NAME2                           TT_CHAR (20)
    NAME3                           CHAR (10 CHAR)
    NAME4                           NCHAR (10)
1 table found.

VARCHAR2

The VARCHAR2 data type specifies a variable length character string. When you define a VARCHAR2 column, you define the maximum number of bytes or characters. Each value is stored exactly as you specify it. The value cannot exceed the maximum length of the column.

You must specify the maximum length. The minimum must be at least one byte. Use the CHAR qualifier to specify the maximum length in characters. For example, VARCHAR2(10 CHAR).

The size of a character ranges from one byte to four bytes depending on the database character set. The BYTE and CHAR qualifiers override the NLS_LENGTH_SEMANTICS parameter setting. For more information on NLS_LENGTH_SEMANTICS, see "ALTER SESSION" and "Setting globalization support attributes" in Oracle TimesTen In-Memory Database Operations Guide.

The NULL value is stored as a single bit for each nullable field within the row. A NOT INLINE VARCHAR2(n) whose value is NULL takes (null bit) + four bytes of storage on 32-bit platforms, whereas an INLINE VARCHAR2(n) whose value is NULL takes (null bit) + four bytes + n bytes of storage, or n more bytes of storage than a NOT INLINE VARCHAR2(n) whose value is NULL. This storage principal holds for all variable length data types: TT_VARCHAR, TT_NVARCHAR, VARCHAR2, NVARCHAR2, VARBINARY.

The following example alters table typedemo, adding columns name5 and name6. The name5 column is defined with type VARCHAR2. The name6 column is defined with TT_VARCHAR. The example illustrates the use of nonpadded comparison semantics with column name5 and blank-padded comparison semantics with column name6:

Command> ALTER TABLE typedemo ADD COLUMN name5 VARCHAR2 (20);
Command> ALTER TABLE typedemo ADD COLUMN name6 TT_VARCHAR (20);
Command> DESCRIBE typedemo;
Table USER.TYPEDEMO:
  Columns:
    NAME                            CHAR (20)
    NAME2                           TT_CHAR (20)
    NAME3                           CHAR (10 CHAR)
    NAME4                           NCHAR (10)
    NAME5                           VARCHAR2 (20) INLINE
    NAME6                           TT_VARCHAR (20) INLINE
1 table found.
(primary key columns are indicated with *)
Command> #Insert SMITH followed by 5 spaces into all columns
Command> INSERT INTO typedemo VALUES
       > ('SMITH     ', 'SMITH     ', 'SMITH     ', 'SMITH     ','SMITH     ',
       > 'SMITH');
1 row inserted.
Command> # Expect 0; Nonpadded comparison semantics
Command> SELECT COUNT (*) FROM typedemo WHERE name5 = 'SMITH';
< 0 >
1 row found.
Command> # Expect 1; Blank-padded comparison semantics
Command> SELECT COUNT (*) FROM typedemo WHERE name6 = 'SMITH';
< 1 >
1 row found.
Command> # Expect 1; Nonpadded comparison semantics
Command> SELECT COUNT (*) FROM typedemo WHERE name5 > 'SMITH';
< 1 >
1 row found.
Command> # Expect 0; Blank-padded comparison semantics
Command> SELECT COUNT (*) FROM typedemo WHERE name6 > 'SMITH';
< 0 >
1 row found.

NVARCHAR2

The NVARCHAR2 data type is a variable length string of two-byte Unicode characters. When you define an NVARCHAR2 column, you define the maximum number of characters. Each value is stored exactly as you specify it. The value cannot exceed the maximum length of the column.

The following example alters table typedemo adding column name7. Data type is NVARCHAR2.

Command> ALTER TABLE typedemo ADD COLUMN Nnme7 NVARCHAR2 (20);
Command> DESCRIBE typedemo;
Table USER1.TYPEDEMO:
  Columns:
    NAME                            CHAR (20)
    NAME2                           TT_CHAR (20)
    NAME3                           CHAR (10 CHAR)
    NAME4                           NCHAR (10)
    NAME5                           VARCHAR2 (20) INLINE
    NAME6                           TT_VARCHAR (20) INLINE
    NAME7                           NVARCHAR2 (20) INLINE
1 table found.

NUMBER

The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10 ^-130 up to but not including 1.0 x 10 ¹²⁶. Each NUMBER value requires from five to 22 bytes.

Specify a fixed-point number as NUMBER(p,s), where the following holds:

• The argument p is the precision or the total number of significant decimal digits, where the most significant digit is the left-most non-zero digit and the least significant digit is the right-most known digit.

• The argument s is the scale, or the number of digits from the decimal point to the least significant digit. The scale ranges from -84 to 127.

  • Positive scale is the number of significant digits to the right of the decimal point up to and including the least significant digit.

  • Negative scale is the number of significant digits to the left of the decimal point up to but not including the least significant digit. For negative scale, the least significant digit is on the left side of the decimal point, because the number is rounded to the specified number of places to the left of the decimal point.

Scale can be greater than precision. For example, in the case of E-notation. When scale is greater than precision, the precision specifies the maximum number of significant digits to the right of the decimal point. For example, if you define the column as type NUMBER(4,5) and you insert .000127 into the column, the value is stored as .00013. A zero is required for the first digit after the decimal point. TimesTen rounds values after the fifth digit to the right of the decimal point.

If a value exceeds the precision, then TimesTen returns an error. If a value exceeds the scale, then TimesTen rounds the value.

NUMBER(p) represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

Specify a floating-point number as NUMBER. If you do not specify precision and scale, TimesTen uses the maximum precision and scale.

The following example alters table numerics by adding columns col6, col7, col8, and col9 defined with the NUMBER data type and specified with different precisions and scales.

Command> ALTER TABLE numerics ADD col6 NUMBER;
Command> ALTER TABLE numerics ADD col7 NUMBER (4,2);
Command> ALTER TABLE numerics ADD col8 NUMBER (4,-2);
Command> ALTER TABLE numerics ADD col8 NUMBER (2,4);
Command> ALTER TABLE numerics ADD col9 NUMBER (2,4);
Command> DESCRIBE numerics;
Table USER1.NUMERICS:
  Columns:
    COL1                            TT_TINYINT
    COL2                            TT_SMALLINT
    COL3                            TT_INTEGER
    COL4                            TT_INTEGER
    COL5                            TT_BIGINT
    COL6                            NUMBER
    COL7                            NUMBER (4,2)
    COL8                            NUMBER (4,-2)
    COL9                            NUMBER (2,4)
1 table found.
(primary key columns are indicated with *)

The next example creates table numbercombo and defines columns with the NUMBER data type using different precisions and scales. The value 123.89 is inserted into the columns.

Command> CREATE TABLE numbercombo (col1 NUMBER, col2 NUMBER (3), 
       > col3 NUMBER (6,2), col4 NUMBER (6,1), col5 NUMBER (6,-2));
Command> DESCRIBE numbercombo;
Table USER1.NUMBERCOMBO:
  Columns:
    COL1                            NUMBER
    COL2                            NUMBER (3)
    COL3                            NUMBER (6,2)
    COL4                            NUMBER (6,1)
    COL5                            NUMBER (6,-2)
1 table found.
(primary key columns are indicated with *)
Command> INSERT INTO numbercombo VALUES (123.89,123.89,123.89,123.89,123.89);
1 row inserted.
Command> VERTICAL ON;
Command> SELECT * FROM numbercombo;
COL1:   123.89
  COL2:   124
  COL3:   123.89
  COL4:   123.9
  COL5:   100
1 row found.

The next example creates a table and defines a column with data type NUMBER(4,2). An attempt to insert a value of 123.89 results in an overflow error.

Command> CREATE TABLE invnumbervalue (col6 NUMBER (4,2));
Command> INSERT INTO invnumbervalue VALUES (123.89);
 2923: Number type value overflow
The command failed.

The next example creates a table and defines columns with the NUMBER data type using a scale that is greater than the precision. Values are inserted into the columns.

Command> CREATE TABLE numbercombo2 (col1 NUMBER (4,5), col2 NUMBER (4,5), 
       > col3 NUMBER (4,5), col4 NUMBER (2,7), col5 NUMBER (2,7), 
       > col6 NUMBER (2,5), col7 NUMBER (2,5));
Command> INSERT INTO numbercombo2 VALUES 
       > (.01234, .00012, .000127, .0000012, .00000123, 1.2e-4, 1.2e-5);
1 row inserted.
Command> DESCRIBE numbercombo2;
Table USER1.NUMBERCOMBO2:
  Columns:
    COL1                            NUMBER (4,5)
    COL2                            NUMBER (4,5)
    COL3                            NUMBER (4,5)
    COL4                            NUMBER (2,7)
    COL5                            NUMBER (2,7)
    COL6                            NUMBER (2,5)
    COL7                            NUMBER (2,5)
1 table found.

(primary key columns are indicated with *)
Command> SELECT * FROM numbercombo2;
COL1:   .01234
  COL2:   .00012
  COL3:   .00013
  COL4:   .0000012
  COL5:   .0000012
  COL6:   .00012
  COL7:   .00001
1 row found.

TT_BIGINT

The TT_BIGINT data type is a signed integer that ranges from -9,223,372,036,854,775,808 (-2⁶³) to 9,223,372,036,854,775,807 (2⁶³-1). It requires eight bytes of storage and thus is more compact than the NUMBER data type. It also has better performance than the NUMBER data type. You cannot specify BIGINT.

This example alters table numerics and attempts to add col5 with a data type of BIGINT. TimesTen generates an error. A second ALTER TABLE successfully adds col5 with the data type TT_BIGINT.

Command> ALTER TABLE numerics ADD COLUMN col5 BIGINT;
 3300: BIGINT is not a valid type name; use TT_BIGINT instead
The command failed.
Command> ALTER TABLE numerics ADD COLUMN col5 TT_BIGINT;
Command> DESCRIBE numerics;
Table USER1.NUMERICS:
  Columns:
    COL1                            TT_TINYINT
    COL2                            TT_SMALLINT
    COL3                            TT_INTEGER
    COL4                            TT_INTEGER
    COL5                            TT_BIGINT
1 table found.
(primary key columns are indicated with *)

TT_INTEGER

The TT_INTEGER data type is a signed integer that ranges from -2,147,483,648 (-2³¹) to 2,147,483,647 (2³¹ -1). It requires four bytes of storage and thus is more compact than the NUMBER data type. It also has better performance than the NUMBER data type. You can specify TT_INT for TT_INTEGER. If you specify either INTEGER or INT, these types are mapped to NUMBER(38).

The following example alters the table numerics and adds col3 with the data type INT. Describing the table shows that the data type is NUMBER(38). The column col3 is dropped. A second ALTER TABLE adds col2 with the data type INTEGER. Describing the table shows that the data type is NUMBER(38). The column col3 is dropped. Columns col3 and col4 are then added with the data types TT_INTEGER and TT_INT. Describing the table shows both data types as TT_INTEGER.

Command> ALTER TABLE numerics ADD col3 INT;
Command> DESCRIBE numerics;
Table USER1.NUMERICS:
  Columns:
    COL1                            TT_TINYINT
    COL2                            TT_SMALLINT
    COL3                            NUMBER (38)
1 table found.
(primary key columns are indicated with *)
Command> ALTER TABLE numerics  col3;
Command> ALTER TABLE numerics ADD col3 INTEGER;
Command> DESCRIBE numerics;
Table USER1.NUMERICS:
Columns:
    COL1                            TT_TINYINT
    COL2                            TT_SMALLINT
    COL3                            NUMBER (38)
1 table found.
(primary key columns are indicated with *)
Command> ALTER TABLE numerics  col3;
Command> ALTER TABLE numerics ADD COLUMN col3 TT_INTEGER;
Command> DESCRIBE numerics;
Table USER1.NUMERICS:
Columns:
    COL1                            TT_TINYINT
    COL2                            TT_SMALLINT
    COL3                            TT_INTEGER
1 table found.
(primary key columns are indicated with *)
Command> ALTER TABLE numerics ADD col4 TT_INT;
Command> DESCRIBE numerics;
Table USER1.NUMERICS:
Columns:
    COL1                            TT_TINYINT
    COL2                            TT_SMALLINT
    COL3                            TT_INTEGER
    COL4                            TT_INTEGER
1 table found.
(primary key columns are indicated with *)

TT_SMALLINT

The TT_SMALLINT data type is a signed integer that ranges from -32,768 (-2¹⁵) to 32,767 (2¹⁵-1). It requires two bytes of storage and thus is more compact than the NUMBER data type. It also has better performance than the NUMBER data type. You can specify the data type SMALLINT, but it maps to NUMBER(38).

The following example alters the table numerics and adds col2 with the data type SMALLINT. Describing the table shows that the data type is NUMBER(38). The column col2 is dropped. A second ALTER TABLE adds col2 with the data type TT_SMALLINT.

Command> ALTER TABLE numerics ADD COLUMN col2 SMALLINT;
Command> DESCRIBE Numerics;
Table USER1.NUMERICS:
  Columns:
    COL1                            TT_TINYINT
    COL2                            NUMBER (38)
1 table found.
(primary key columns are indicated with *)
Command> ALTER TABLE numerics  COLUMN col2;
Command> ALTER TABLE numerics ADD COLUMN col2 TT_SMALLINT;
Command> DESCRIBE numerics;
Table USER1.NUMERICS:
  Columns:
    COL1                            TT_TINYINT
    COL2                            TT_SMALLINT
1 table found.
(primary key columns are indicated with *)

TT_TINYINT

The TT_TINYINT data type is an unsigned integer that ranges from 0 to 255 (2⁸ -1). It requires one byte of storage and thus is more compact than the NUMBER data type. It also has better performance than the NUMBER data type. The data type of a negative TT_TINYINT is TT_SMALLINT. You cannot specify TINYINT.

The following example first attempts to create a table named numerics that defines a column named col1 with data type TINYINT. TimesTen returns an error. The example then redefines the column with data type TT_TINYINT.

Command> CREATE TABLE numerics (col1 TINYINT);
 3300: TINYINT is not a valid type name; use TT_TINYINT instead
The command failed.
Command> CREATE TABLE numerics (col1 TT_TINYINT);
Command> DESCRIBE numerics;
Table USER1.NUMERICS:
  Columns:
    COL1                            TT_TINYINT
1 table found.
(primary key columns are indicated with *)

Floating-point numbers

Floating-point numbers can be with or without a decimal point. An exponent may be used to increase the range (for example, 1.2E-20).

Floating-point numbers do not have a scale because the number of digits that can appear after the decimal point is not restricted.

Binary floating-point numbers are stored using binary precision (the digits 0 and 1). For the NUMBER data type, values are stored using decimal precision (the digits 0 through 9).

Literal values that are within the range and precision supported by NUMBER are stored as NUMBER because literals are expressed using decimal precision.

Use one of the following data types for floating-point numbers:

• BINARY_DOUBLE

• BINARY_FLOAT

• FLOAT and FLOAT(n)

Command> CREATE TABLE BfBd (Col1 BINARY_FLOAT, Col2 BINARY_DOUBLE);
Command> DESCRIBE BfBd;
Table UISER1.BFBD:
  Columns:
    COL1                            BINARY_FLOAT
    COL2                            BINARY_DOUBLE
1 table found.
(primary key columns are indicated with *)

BLOB

The Binary LOB (BLOB) data type stores unstructured binary large objects. The maximum size for BLOB data is 16 MB.

When you define a BLOB in a column, you do not define the maximum number of characters as you would with VARBINARY and other variable length data types. Instead, the definition for the column would be as follows:

Command> CREATE TABLE blob_content ( 
 > id NUMBER PRIMARY KEY, 
 > blob_column BLOB ); 

To manipulate a BLOB, the following functions are provided:

• There are two methods to initialize a BLOB, including the EMPTY_BLOB function to initialize an empty BLOB. For details on initializing a BLOB, see "Initializing LOBs". For details on how an empty LOB is different from a NULL LOB, see "Difference between NULL and empty LOBs".

• To convert a binary value to a BLOB, use the TO_LOB or TO_BLOB functions. See "TO_BLOB" and "TO_LOB" for more details.

CLOB

The Character LOB (CLOB) data type stores single-byte and multibyte character data. The maximum size for CLOB data is 4 MB. The maximum number of characters that can be stored in the CLOB depends on whether you are using a single or multibyte character set.

When you define a CLOB in a column, you do not define the maximum number of characters as you would with VARCHAR and other variable length data types. Instead, the definition for the column would be as follows:

Command> CREATE TABLE clob_content ( 
 > id NUMBER PRIMARY KEY, 
 > clob_column CLOB ); 

To manipulate a CLOB, the following functions are provided:

• There are two methods to initialize a CLOB, including the EMPTY_CLOB function to initialize an empty CLOB. For details on initializing a CLOB, see "Initializing LOBs". For details on how an empty LOB is different from a NULL LOB, see "Difference between NULL and empty LOBs".

• To convert a character string to a CLOB, use the TO_LOB or TO_CLOB functions. See "TO_CLOB" and "TO_LOB" for more details.

NCLOB

The National Character LOB (NCLOB) data type stores Unicode data. The maximum size for an NCLOB data is 4 MB.

When you define a NCLOB in a column, you do not define the maximum number of characters as you would with VARCHAR and other variable length data types. Instead, the definition for the column would be as follows:

Command> CREATE TABLE nclob_content ( 
 > id NUMBER PRIMARY KEY, 
 > nclob_column NCLOB ); 

The following functions support the NCLOB data type:

• There are two methods to initialize an NCLOB, including the EMPTY_CLOB function to initialize an empty NCLOB. For details on initializing a NCLOB, see "Initializing LOBs". For details on how an empty LOB is different from a NULL LOB, see "Difference between NULL and empty LOBs".

• To convert a character string to an NCLOB, use the TO_LOB or TO_CLOB functions. See "TO_CLOB" and "TO_LOB" for more details.

Difference between NULL and empty LOBs

A NULL LOB is not the same as an empty LOB.

• A NULL LOB has the value of NULL, so NULL is returned if you request a NULL LOB.

• An empty LOB is initialized with either the EMPTY_CLOB or EMPTY_BLOB functions. These functions initialize the LOB to be a zero-length, non-NULL value. You can also use the EMPTY_CLOB or EMPTY_BLOB functions to initialize a LOB in a non-nullable column.

Initializing LOBs

You can initialize a LOB in one of two ways:

• You can insert an empty LOB into a BLOB, CLOB or NCLOB column by using the EMPTY_BLOB or EMPTY_CLOB functions. This is useful when you do not have any data, but want to create the LOB in preparation for data. It is also useful for initializing non-nullable LOB columns.

• Initialize the LOB by inserting data directly. There is no need to initialize a LOB using the EMPTY_BLOB or EMPTY_CLOB functions, you can simply insert the data directly.

The following demonstrates examples of each type of initialization:

You can initialize a LOB with the EMPTY_CLOB function, as shown with the following example:

Command> INSERT INTO clob_content (id, clob_column)
       > VALUES (1, EMPTY_CLOB( ) );
1 row inserted.

You can initialize a LOB by inserting data directly, as shown with the following example:

Command> INSERT INTO clob_content(id, clob_column)
 > VALUES (4, 'Demonstration of the LOB initialization.');
1 row inserted.

You can initialize or update an existing LOB value with the UPDATE statement, as shown with the following examples:

Command> UPDATE blob_content
 > SET blob_column = 0x000AF4511
 > WHERE id = 1;
1 row updated.

Command> select * from blob_content;
 < 1, 0000AF4511 >
1 rows found.

Command> UPDATE clob_content
 > SET clob_column = 'Demonstration of the CLOB data type '
 > WHERE id = 1;
1 row updated.

Command> SELECT * FROM clob_content;
 < 1, Demonstration of the CLOB data type >

DATE

The format of a DATE value is YYYY-MM-DD HH:MI:SS and ranges from -4712-01-01 (January 1, 4712 BC) to 9999-12-31 (December 31, 9999 AD). There are no fractional seconds. The DATE type requires seven bytes of storage.

TimesTen does not support user-specified NLS_DATE_FORMAT settings. The SQL TO_CHAR and TO_DATE functions can be used to specify other formats.

TIME

The format of a TIME value is HH:MI:SS and ranges from 00:00:00 (midnight) to 23:59:59 (11:59:59 pm). The TIME data type requires eight bytes of storage.

TIMESTAMP

The format of a TIMESTAMP value is YYYY-MM-DD HH:MI:SS [.FFFFFFFFF]. The fractional seconds precision range is 0 to 9. The default is 6. The date range is from -4712-01-01 (January 1, 4712 BC) to 9999-12-31 (December 31, 9999 AD). The TIMESTAMP type requires 12 bytes of storage. The TIMESTAMP type has a larger date range and supports more precision than TT_TIMESTAMP.

TimesTen does not support user-specified NLS_TIMESTAMP_FORMAT settings. The SQL TO_CHAR and TO_DATE functions can be used to specify other formats.

TT_DATE

The format of a TT_DATE value is YYYY-MM-DD and ranges from 1753-01-01 (January 1, 1753 AD) to 9999-12-31 (December 31, 9999 AD). The TT_DATE data type requires four bytes of storage.

TT_TIMESTAMP

The format of a TT_TIMESTAMP value is YYYY-MM-DD HH:MI:SS [.FFFFFFFFF]. The fractional seconds precision is 6. The range is from 1753-01-01 00:00:00 (January 1, 1753, midnight) to 9999-12-31 23:59:59 (December 31, 9999, 11:59:59 PM). The TT_TIMESTAMP type requires eight bytes of storage. TT_TIMESTAMP is faster than the TIMESTAMP data type and has a smaller storage size.

Using interval data types

If you are using TimesTen type mode, refer to the Oracle TimesTen In-Memory Database API and SQL Reference Guide, Release 6.0.3, for information on interval 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';

Handling timezone 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

If you are using TimesTen type mode, see Oracle TimesTen In-Memory Database API and SQL Reference Guide, Release 6.0.3, for information about datetime and interval 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 ORA_DATE or ORA_TIMESTAMP value. TimesTen internally converts ORA_TIMESTAMP values to ORA_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-5 is a matrix of datetime arithmetic operations. Dashes represent operations that are not supported. The matrix assumes that you are using Oracle type mode:

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.

Numeric values

A larger value is greater than a smaller value: -1 is less than 10, and -10 is less than -1.

The floating-point value NaN is greater than any other numeric value and is equal to itself.

Date values

A later date is considered greater than an earlier one. For example, the date equivalent of '10-AUG-2005' is less than that of '30-AUG-2006', and '30-AUG-2006 1:15 pm' is greater than '30-AUG-2006 10:10 am'.

Character values

Character values are compared in the following ways:

• Binary and linguistic sorting

• Blank-padded and nonpadded comparison semantics

'a   ' = 'a'

'a   ' > 'a'

Implicit data type conversion

TimesTen converts a value from one data type to another when such a conversion makes sense.

Table 1-7 and Table 1-8 use a matrix to illustrate TimesTen implicit data type conversions. YES in the cell indicates the conversion is supported. NO in the cell indicates the conversion is not supported. The rules for implicit conversion follow the table.

The following rules apply:

• During arithmetic operations on and comparisons between character and non-character data types, TimesTen converts from any character data type to a numeric or datetime data type as appropriate. In arithmetic operations between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, TimesTen converts to a NUMBER.

• During arithmetic operations, floating point values INF and NAN are not supported when converting character values to numeric values.

• During concatenation operations, TimesTen converts non-character data types to CHAR, NCHAR, VARCHAR2, or NVARCHAR2 depending on the other operand.

• When comparing a character value with a numeric value, TimesTen converts the character data to a numeric value.

• When comparing a character value with a datetime value, TimesTen converts the character data to a datetime value.

• During conversion from a timestamp value to a DATE value, the fractional seconds portion of the timestamp value is truncated.

• Conversions from BINARY_FLOAT to BINARY_DOUBLE are exact.

• Conversions from BINARY_DOUBLE to BINARY_FLOAT are inexact if the BINARY_DOUBLE value uses more bits of precision that supported by the BINARY_FLOAT.

• Conversions between either character values or exact numeric values (TT_TINYINT, TT_SMALLINT, TT_INTEGER, TT_BIGINT, NUMBER) and floating-point values (BINARY_FLOAT, BINARY_DOUBLE) can be inexact because the character values and the exact numeric values use decimal precision whereas the floating-point numbers use binary precision.

• When manipulating numeric values, TimesTen usually adjusts precision and scale to allow for maximum capacity. In such cases, the numeric data type resulting from such operations can differ from the numeric data type found in the underlying tables.

• When making assignments, TimesTen converts the value on the right side of the equal sign (=) to the data type of the target of the assignment on the left side.

• When you use a SQL function or operator with an argument of a data type other than the one it accepts, TimesTen converts the argument to the accepted data type so long as TimesTen supports the implicit conversion. For more information on supported data type conversions, see Implicit data type conversion.

• During INSERT, INSERT... SELECT, and UPDATE operations, TimesTen converts the value to the data type of the affected column.

• Implicit and explicit CHAR/VARCHAR2 <-> NCHAR/NVARCHAR2 conversions are supported except when the character set is TIMESTEN8. An example of implicit conversion:

  Command> CREATE TABLE convdemo (c1 CHAR (10), x1 TT_INTEGER);
  Command> CREATE TABLE convdemo2 (c1 NCHAR (10), x2 TT_INTEGER);
  Command> INSERT INTO convdemo VALUES ('ABC', 10);
  1 row inserted.
  Command> INSERT INTO convdemo VALUES ('def', 100);
  1 row inserted.
  Command> INSERT INTO convdemo2 SELECT * FROM convdemo;
  2 rows inserted.
  Command> SELECT x1,x2,convdemo.c1, convdemo2.c1 
         > FROM convdemo, convdemo2 where Convdemo.c1 = convdemo2.c1;
  X1, X2, C1, C1
  < 10, 10, ABC       , ABC        >
  < 100, 100, def       , def        >
  2 rows found.
  

Constant values

You can use constant values in places where a floating-point constant is allowed. The following constants are supported:

• BINARY_FLOAT_INFINITY

• -BINARY_FLOAT_INFINITY

• BINARY_DOUBLE_INFINITY

• -BINARY_DOUBLE_INFINITY

• BINARY_FLOAT_NAN

• BINARY_DOUBLE_NAN

In the following example, a table is created with a column of type BINARY_FLOAT and a column of type TT_INTEGER. BINARY_FLOAT_INFINITY and BINARY_FLOAT_NAN are inserted into the column of type BINARY_FLOAT.

Command> CREATE TABLE bfdemo (id BINARY_FLOAT, Ii2 TT_INTEGER);
Command> INSERT INTO bfdemo VALUES (BINARY_FLOAT_INFINITY, 50);
1 row inserted.
Command> INSERT INTO bfdemo VALUES (BINARY_FLOAT_NAN, 100);
1 row inserted.
Command> SELECT * FROM bfdemo;
< INF, 50 >
< NAN, 100 >
2 rows found.

Primary key values

Inf, -Inf, and NaN are acceptable values in columns defined with a primary key. This is different from NULL, which is not allowed in columns defined with a primary key.

You can only insert Inf, -Inf, and NaN values into BINARY_FLOAT and BINARY_DOUBLE columns.

Selecting Inf and NaN (floating-point conditions)

Floating-point conditions determine whether an expression is infinite or is the undefined result of an operation (NaN, meaning not a number).

Consider the following syntax:

Expression IS [NOT] {NAN|INFINITE}

Expression must either resolve to a numeric data type or to a data type that can be implicitly converted to a numeric data type.

The following table describes the floating-point conditions.

SELECT * FROM bfdemo WHERE id IS
NOT NAN;
ID, ID2
< INF, 50 >
1 row found.

SELECT * FROM bfdemo WHERE id IS
NOT INFINITE;
ID, ID2
< NAN, 100 >
1 row found.

The following rules apply to comparisons with Inf and NaN:

• Comparison between Inf (or -Inf) and a finite value are as expected. For example, 5 > -Inf.

• (Inf = Inf) and (Inf > -Inf) both evaluate to TRUE.

• (NaN = NaN) evaluates to TRUE.

In reference to collating sequences:

• -Inf sorts lower than any other value.

• Inf sorts lower than NaN and NULL and higher than any other value.

• NaN sorts higher than Inf.

• NULL sorts higher than NaN. NULL is always the largest value in any collating sequence.

Expressions involving Inf and NaN

• Expressions containing floating-point values may generate Inf, -Inf, or NaN. This can occur either because the expression generated overflow or exceptional conditions or because one or more of the values in the expression was Inf, -Inf, or NaN. Inf and NaN are generated in overflow or division-by-zero conditions.

• Inf, -Inf, and NaN values are not ignored in aggregate functions. NULL values are. If you want to exclude Inf and NaN from aggregates, or from any SELECT result, use both the IS NOT NAN and IS NOT INFINITE predicates.

Data types supported in TimesTen type mode

Oracle data types supported in TimesTen type mode