Numeric Data Types

Numeric types store positive and negative fixed and floating-point numbers, zero, infinity, and values that are the undefined result of an operation (NaN, meaning not a number).

TimesTen supports both exact and approximate numeric data types. Arithmetic operations can be performed on numeric types only. Similarly, SUM and AVG aggregates require numeric types.

Following are the exact numeric types.

The approximate types are:

NUMBER

The NUMBER data type stores both zero and positive and negative fixed numbers with absolute values from 1.0 x 10 -130 up to but not including 1.0 x 10 126. 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 nonzero 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 (-263) to 9,223,372,036,854,775,807 (263-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 (-231) to 2,147,483,647 (231 -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 (-215) to 32,767 (215-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 (28 -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_DOUBLE is a 64-bit, double-precision, floating-point number.

Both BINARY_FLOAT and BINARY_DOUBLE support the special values Inf, -Inf, and NaN (not a number) and conform to the IEEE standard.

Floating-point number limits:

  • BINARY_FLOAT

    • Minimum positive finite value: 1.17549E-38F

    • Maximum positive finite value: 3.40282E+38F

  • BINARY_DOUBLE

    • Minimum positive finite value: 2.22507485850720E-308

    • Maximum positive finite value: 1.79769313486231E+308

The following example creates a table and defines two columns with the BINARY_FLOAT and BINARY_DOUBLE data types. 

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 *)

BINARY_FLOAT

BINARY_FLOAT is a 32-bit, single-precision, floating-point number.

FLOAT and FLOAT(n)

TimesTen also supports the ANSI type FLOAT. FLOAT is an exact numeric type and is implemented as the NUMBER type. The value of n indicates the number of bits of precision that can be stored, from 1 to 126. To convert from binary precision to decimal precision, multiply n by 0.30103. To convert from decimal precision to binary precision, multiply the decimal precision by 3.32193. The maximum 126 digits of binary precision is equivalent to approximately 38 digits of decimal precision.