SQLstream Data Types

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > Basic Building Blocks >

SQLstream Data Types

Previous pageReturn to chapter overviewNext page

The following table summarizes the data types supported by SQLstream. Data coming into the s-Server, as through the Extensible Common Data Framework, needs to match column types for columns in s-Server objects, such as foreign streams.

Data type

Description

Notes

BIGINT

64-bit signed integer

Range is  -19223372036854775808 to 9223372036854775807

[2**(-63) to 2**63]

BINARY

Binary (non character) data

Substring works on BINARY. Concatenation does not work on BINARY.

BOOLEAN

TRUE, FALSE, or NULL

Evaluates to TRUE, FALSE, and UNKNOWN.

CHAR (n)

A character string of fixed length n. Also specifiable as CHARACTER

n must be greater than 0 and less than 65535.

DATE

A date is a calendar day (year/month/day).

Precision is day. A valid DATE ranges from 0001-01-01 to 9999-12-31.

DECIMAL

DEC

NUMERIC

A fixed point, with up to 19 significant digits.

Can be specified with DECIMAL, DEC, or NUMERIC.

The range of a DECIMAL type with precision 19 and scale 0 is the range of a BIGINT.

DOUBLE

DOUBLE PRECISION

A 64-bit floating point number

64-bit approx value; -1.79E+308 to 1.79E+308. Follows the ISO DOUBLE PRECISION data type, 53 bits are used for the number's mantissa in scientific notation, representing 15 digits of precision and 8 bytes of storage.

INTEGER

INT

See also BIGINT, SMALLINT, and TINYINT.

32-bit signed integer. Range is -2147483648 to 2147483647 [ 2**(-63) to 2**63 - 1]

INTERVAL <timeunit> [TO <timeunit>]

Day-time intervals supported, year-month intervals not supported

Allowed in an expression in date arithmetic, but cannot be used as a datatype for a column in a table or stream.See Examples.

<timeUnit>

The units of a INTERVAL value

Supported units are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND

SMALLINT

16-bit signed integer

Range is -32768 to 32767

[2**(-15) to 2**15 - 1]

REAL

A 32-bit floating point number

Following the ISO REAL data type, 24 bits are used for the number's mantissa in scientific notation, representing 7 digits of precision and 4 bytes of storage. The minimum value is -3.40E+38; the maximum value is 3.40E+38.

TIME

A TIME is a time in a day (hour:minute:second).

Its precision is milliseconds; its range is 00:00:00.000 to 23:59:59.999. Since the system clock runs in UTC, the timezone used for values stored in a TIME or TIMESTAMP column is not considered.

for values stored in a TIME or TIMESTAMP column.

TIMESTAMP

A TIMESTAMP is a combined DATE and TIME.

A TIMESTAMP value always has a precision of 1 millisecond. It has no particular timezone. Since the system clock runs in UTC, the timezone used for values stored in a TIME or TIMESTAMP column is not considered. Its range runs from the largest value, approximately +229 (in years) to the smallest value, -229. Each timestamp is stored as a signed 64-bit integer, with 0 representing the Unix epoch (Jan 1, 1970 00:00am). This means that the largest TIMESTAMP value represents approximately 300 million years after 1970, and the smallest value represents approximately 300 million years before 1970. Following the SQL standard, a TIMESTAMP value has an undefined timezone.

TINYINT

8-bit signed integer

Range is -128 to 127,

VARBINARY (n)

Also specifiable as BINARY VARYING

n must be greater than 0 and less than 65535.

VARCHAR (n)

Also specifiable as CHARACTER VARYING

n must be greater than 0 and less than 65535.

s-Server supports full unicode by letting you set character set "UTF16".

For example, you can specify a column along the following lines:

s1 varchar(100) character set "UTF16",

You can then insert full unicode strings into streams. with this designation. Unicode literals can be specified by preceding the literal with U&.

insert into T(s1) values (U&'sdfÂÒÒØØô?');

Note: Currently lengths of CHAR and VARCHAR fields are specified in maximum number of UTF16 byte pairs. This is usually the same as the number of characters unless 4 byte characters are used (outside of the unicode basic multilingual plane).

Notes

Regarding characters:

SQLstream supports only Java single-byte CHARACTER SETs.
Implicit type conversion is not supported. That is, characters are mutually assignable if and only if they are taken from the same character repertoire and are values of the data types CHARACTER or CHARACTER VARYING.

Regarding numbers:

Numbers are mutually comparable and mutually assignable if they are values of the data types NUMERIC, DECIMAL, INTEGER, BIGINT, SMALLINT, TINYINT, REAL, and DOUBLE PRECISION.

The following sets of data types are synonyms:

DEC and DECIMAL
DOUBLE PRECISION and DOUBLE
CHARACTER and CHAR
CHAR VARYING or CHARACTER VARYING and VARCHAR
BINARY VARYING and VARBINARY
INT and INTEGER
Binary values (data types BINARY and BINARY VARYING) are always mutually comparable and are mutually assignable.

Regarding dates, times, and timestamps:

Implicit type conversion is not supported, i.e., datetime values are mutually assignable only if the source and target of the assignment are both of type DATE, or both of type TIME, or both of type TIMESTAMP.
The SQLstream s-Server's timezone is always UTC. The time functions, including the SQLstream extension CURRENT_ROW_TIMESTAMP, return time in UTC.

Example Date, Time and Interval Literals

0: jdbc:sqlstream:engine:> values DATE '2007-02-19';

EXPR$0  2007-02-19

0: jdbc:sqlstream:engine:> values TIME '21:23:45';

EXPR$0  21:23:45

0: jdbc:sqlstream:engine:> values TIMESTAMP '2007-02-19 21:23:45';

EXPR$0  2007-02-19 21:23:45.0

0: jdbc:sqlstream:engine:> values INTERVAL '1:30' hour to minute;

EXPR$0  +1:30

0: jdbc:sqlstream:engine:> values INTERVAL '45 1:30' day to minute;

EXPR$0  +45 01:30

0: jdbc:sqlstream:engine:> values INTERVAL '4544 1:30' day(4) to minute;

EXPR$0  +4544 01:30

0: jdbc:sqlstream:engine:> values INTERVAL '4544 1:30:45.345' day(4) to second(3);

EXPR$0  +4544 01:30:45.345