Type Specifications

Table 1-1 shows the supported data types in TimesTen.

Table 1-1 Supported data types

Data type	Description
BINARY(n)	Fixed-length binary value of n bytes Supported values for n range from 1 to 8300. BINARY data is padded to the maximum column size with trailing zeroes. Alternatively, specify TT_BINARY(n). See BINARY and VARBINARY Data Types for details.
BINARY_DOUBLE	A 64-bit floating-point number BINARY_DOUBLE is a double-precision native floating point number that supports +Inf, -Inf, and NaN values. BINARY_DOUBLE is an approximate numeric value consisting of an exponent and mantissa. You can use exponential or E-notation. BINARY_DOUBLE has binary precision 53. Minimum positive finite value: 2.22507485850720E-308 Maximum positive finite value: 1.79769313486231E+308 See BINARY_DOUBLE for details.
BINARY_FLOAT	A 32-bit floating-point number BINARY_FLOAT is a single-precision native floating-point type that supports +Inf, -Inf, and NaN values. BINARY_FLOAT is an approximate numeric value consisting of an exponent and mantissa. You can use exponential or E-notation. BINARY_FLOAT has binary precision 24. Minimum positive finite value: 1.17549E-38F Maximum positive finite value: 3.40282E+38F See "BINARY_FLOAT" for details.
BLOB	A variable-length binary large object. The valid range is from 1 to 16,777,216 bytes. See BLOB for details.
CHAR[ACTER][(n[BYTE|CHAR])]	Fixed-length character string of length n bytes or n characters. BYTE indicates that the column has byte-length semantics. The valid values for n range from a minimum of one byte to a maximum of 8300 bytes. CHAR indicates that the column has character-length semantics. The minimum CHAR length is one character. The maximum CHAR length depends on how many characters fit in 8300 bytes. This is determined by the database character set in use. For character set AL32UTF8, up to four bytes per character may be needed, so the CHAR length limit ranges from 2075 to 8300 depending on the character set. If you do not specify BYTE or CHAR, the default is BYTE. If you do not specify n, the default is 1. A zero-length string is interpreted as NULL. CHAR data is padded to the maximum column size with trailing blanks. Blank-padded comparison semantics are used. See CHAR for details.
CLOB	A variable-length character large object containing single-byte or multibyte characters. The valid range is from 1 to 4,194,304 bytes of data depending on the database character set. Specifically, the maximum size of a CLOB is stated in bytes (4,194,304 bytes), but the CLOB data type stores characters. Depending on the database character set and the actual characters being stored, the CLOB data type can store between 1,048,576 characters and 4,194,304 characters. See CLOB for details.
DATE	Date and time information: century, year, month, day, hour, minute, and second Format is: YYYY-MM-DD HHMISS. Valid date range is from January 1, 4712 BC to December 31, 9999 AD. There are no fractional seconds. See DATE for details.
INTERVAL [+/-] IntervalQualifier	Interval type TimesTen partially supports interval types, expressed with the type INTERVAL and an IntervalQualifier. An IntervalQualifier can only specify a single field type with no precision. The default leading precision is eight digits for all interval types. The single field type can be: year, month, day, hour, minute, or second. Currently, interval types can be specified only with a constant. Note: You cannot specify a column of an interval type. These are non-persistent types used in SQL expressions at runtime. In addition, for those comparisons where an interval data type is returned, the 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. See TimesTen Intervals for details.
NCHAR[(n)]	Fixed-length string of n two-byte Unicode characters The number of bytes required is 2*n where n is the specified number of characters. NCHAR character limits are half the byte limits so the maximum size is 4150. A zero-length string is interpreted as NULL. NCHAR data is padded to the maximum column size with U+0020 SPACE. Blank-padded comparison semantics are used. See NCHAR for details.
NCLOB	A national variable-length character large object containing Unicode characters. The valid range is from 1 to 2,097,152 characters. See NCLOB for details.
NUMBER[(p [,s])]	Number having precision and scale. The precision ranges from 1 to 38 decimal. The scale ranges from -84 to 127. Both precision and scale are optional. If you do not specify a precision or a scale, TimesTen assumes the maximum precision of 38 and flexible scale. NUMBER supports negative scale and scale greater than precision. NUMBER stores both zero and positive and negative fixed numbers with absolute values from 1.0 x 10-130 to (but not including) 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then TimesTen returns an error. See NUMBER for details.
NVARCHAR2(n)	Variable-length string of n two-byte Unicode characters. The number of bytes required is 2*n where n is the specified number of characters. NVARCHAR2 character limits are half the byte limits. The valid range is from 1 to 2,097,152 characters. You must specify n. A zero-length string is interpreted as NULL. Nonpadded comparison semantics are used. See NVARCHAR2 for details.
ROWID	An 18-byte character string that represents the address of a table row or materialized view row in TimesTen Classic. Specify a literal ROWID value as a CHAR constant enclosed in single quotes. See ROWID Data Type for details.
TIME	A time of day between 00:00:00 (midnight) and 23:59:59 (11:59:59 pm), inclusive The format is: HH:MI:SS. Alternatively, specify TT_TIME. See TIME for details.
TIMESTAMP [(fractional_seconds_precision)]	Year, month, and day values of the date plus hour, minute, and second values of the time The fractional_seconds_precision is the number of digits in the fractional part of the seconds field. Valid date range is from January 1, 4712 BC to December 31, 9999 AD. TT_TIMESTAMP has a smaller storage size than TIMESTAMP. TT_TIMESTAMP is faster than TIMESTAMP because TT_TIMESTAMP is an eight-byte integer containing the number of microseconds since January 1, 1753. Comparisons are very fast. TIMESTAMP has a larger range than TT_TIMESTAMP in that TIMESTAMP can store date and time data as far back as 4712 BC. TIMESTAMP also supports up to nine digits of fractional second precision whereas TT_TIMESTAMP supports six digits of fractional second precision. The fractional seconds precision range is 0 to 9. The default is 6. Format is: YYYY-MM-DD HH:MI:SS [.FFFFFFFFF] See TIMESTAMP for details.
TT_BIGINT	A signed eight-byte integer in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Use TT_BIGINT rather than the NUMBER data type. TT_BIGINT is more compact and offers faster performance than the NUMBER type. If you need to store greater than 19-digit integers, use NUMBER(p) where p > 19. See TT_BIGINT for details.
TT_DATE	Date information: century, year, month, and day The format is YYYY-MM-DD, where MM is expressed as an integer such as 2006-10-28. Valid dates are between 1753-01-01 (January 1, 1753) and 9999-12-31 (December 31, 9999). See TT_DATE for details.
TT_INTEGER	A signed integer in the range -2,147,483,648 to 2,147,483,647. TT_INTEGER is a native signed integer data type. Use TT_INTEGER rather than INTEGER. INTEGER maps to the NUMBER data type. TT_INTEGER is more compact and offers faster performance than the NUMBER type. If you need to store greater than 19-digit integers, use NUMBER(p) where p > 19. See TT_INTEGER for details.
TT_SMALLINT	A native signed 16-bit integer in the range -32,768 to 32,767. Use TT_SMALLINT rather than SMALLINT. SMALLINT maps to the NUMBER data type. TT_SMALLINT is more compact and offers faster performance than the NUMBER type. If you need to store greater than 19-digit integers, use NUMBER(p) where p > 19. See TT_SMALLINT for details.
TT_TIMESTAMP	A date and time between 1753-01-01 00:00:00 (midnight on January 1, 1753) and 9999-12-31 23:59:59 pm (11:59:59 pm on December 31, 9999), inclusive Any values for the fraction not specified in full microseconds result in a "Data Truncated" error. The format is YYYY-MM-DD HH:MI:SS [.FFFFFFFFF]. TT_TIMESTAMP has a smaller storage size than TIMESTAMP and is faster than TIMESTAMP because TT_TIMESTAMP is an eight-byte integer containing the number of microseconds since January 1, 1753. Comparisons are very fast. TIMESTAMP has a larger range than TT_TIMESTAMP in that TIMESTAMP can store date and time data as far back as 4712 BC. TIMESTAMP also supports up to nine digits of fractional second precision whereas TT_TIMESTAMP supports six digits of fractional second precision. You can specify TT_TIMESTAMP(6). See TT_TIMESTAMP for details.
TT_TINYINT	Unsigned integer ranging from 0 to 255. Use TT_TINYINT rather than the NUMBER data type. TT_TINYINT is more compact and offers faster performance than the NUMBER type. If you need to store greater than 19-digit integers, use NUMBER(p) where p > 19. Since TT_TINYINT is unsigned, the negation of a TT_TINYINT is a TT_SMALLINT. See TT_TINYINT for details.
VARBINARY(n)	Variable-length binary value with length n bytes. The valid range is from 1 to 4,194,304 bytes. Alternatively, specify TT_VARBINARY(n). See BINARY and VARBINARY Data Types for details.
VARCHAR[2](n[BYTE|CHAR])	Variable-length character string with a length of n bytes or n characters. BYTE indicates that the column has byte-length semantics. CHAR indicates that the column has character-length semantics. For byte-length semantics, the valid range is from 1 to 4,194,304 bytes. For character-length semantics, the valid range is from 1 to 1,048,576 characters. If you do not specify BYTE or CHAR, the default is BYTE. You must specify a value for n. A zero-length string is interpreted as NULL. Nonpadded comparison semantics are used. Do not use the VARCHAR type. Although it is currently synonymous with VARCHAR2, the VARCHAR type is scheduled to be redefined. See VARCHAR2 for details.