Data Types

This page covers the following subtopics:

Supported Data Types

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 (-263 to 263)
BINARY Binary (non character) data Range is 0 to 1048575. Substring works on BINARY. Concatenation does not work on BINARY.
BOOLEAN TRUE, FALSE, or NULL Evaluates to TRUE, FALSE, and UNKNOWN.
CHAR[ACTER](n) A character string of fixed length n. n must be greater than 0 and (by default) less than 65535. See also setMaximumCharacterLength().
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.
DEC[IMAL] [ (scale [,prec]) ]
NUMERIC [ (scale [,prec]) ]
A fixed point, with up to 19 significant digits. Default scale is 19, default precision is 0 (an integer). The range of a DECIMAL type with precision 19 and scale 0 is the range of a BIGINT.
DOUBLE [PRECISION]
FLOAT
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.

32 bit integer has a range of -231 to 231 – 1
INT[EGER] 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.
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 (-231 to 231 – 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. Precision is milliseconds; 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 (-27 to 27 – 1)
VARBINARY(n)
BINARY VARYING(n)
Variable length binary n must be greater than 0 and less than 1048575.
VARCHAR(n)
CHAR[ACTER] VARYING(n)
Variable length character n must be greater than 0 and (by default) less than 1048575. See also setMaximumCharacterLength().

General Notes

The following sets of data types are synonyms:

  • DEC, DECIMAL and NUMERIC
  • DOUBLE PRECISION, DOUBLE and FLOAT
  • CHARACTER and CHAR
  • CHAR VARYING, 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.

Notes on CHAR and VARCHAR

  • 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.
  • The maximum allowed length of a CHAR or VARCHAR data type can be increased using the setMaximumCharacterLength() procedure.
  • SQLstream allocates up to 4 x character length as the maxByteLength for a column as UTF-8 characters occupy from 1 to 4 bytes each.

s-Server supports the Unicode character set "UTF-8" . For example, you can specify columns along the following lines:

c1 char(100) character set "UTF-8",

SQLstream CHAR and VARCHAR columns always use the UTF-8 character set, whether explicitly specified or not.

All SQLstream plugins support reading and writing files and payloads using any character set. Column values are mapped to UTF-8 format internally.

You can insert Unicode strings into streams. Unicode literals can be specified by preceding the literal with U&. This example inserts the Unicode U+00BD (a ½ symbol):

insert into MyStream(vc1) values (U&'\00BD'); 

For more information on character literals see Character Expressions and Literals.

Notes on Number types

  • 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.

Numeric Types and Precision

For DECIMAL we support a maximum of 19 digits for precision and scale.

  • Precision specifies the maximum number of decimal digits that can be stored in the column, both to the right and to the left of the decimal point. You can specify precisions ranging from 1 digit to 19 digits or use the default precision of 19 digits.

  • Scale specifies the maximum number of digits that can be stored to the right of the decimal point. Scale must be less than or equal to the precision. You can specify a scale ranging from 0 digits to 19 digits, or use the default scale of 0 digits.

Here are the rules showing how precision and scale are used:

Rule for Divide

Let p1, s1 be the precision and scale of the first operand, such as DECIMAL (10,1).

Let p2, s2 be the precision and scale of the second operand, such as DECIMAL (10,3).

Let p, s be the precision and scale of the result.

Let d be the number of whole digits in the result.

Then the result type is a decimal with:

d = p1 - s1 + s2
D = 10 - 1 + 3

Number of whole digits in result = 6

s <= MAX (6, s1 + p2 +1)
S <= MAX (6, 1 + 10 + 1)

Scale of result = 14

p = d + s

Precision of result = 19

Precision and scale are capped at their maximum values (19, where scale cannot be larger than precision).

Precedence is first giving at least the >= s1 followed by enough whole digits to represent the result without overflow.

Rule for Multiply

Let p1, s1 be the precision and scale of the first operand DECIMAL (10,1).

Let p2, s2 be the precision and scale of the second operand DECIMAL (10,3).

Let p, s be the precision and scale of the result.

Then the result type is a decimal with:

p = p1 + p2
p = 10 + 10

Precision of result = 19

s = s1 + s2
s = 1 + 3

Scale of result = 4

Precision and scale are capped at their maximum values (19, where scale cannot be larger than precision).

Rule for Addition or Subtraction

Type-inference strategy whereby the result type of a call is the decimal sum of two exact numeric operands where at least one of the operands is a decimal.

Let p1, s1 be the precision and scale of the first operand DECIMAL (10,1).

Let p2, s2 be the precision and scale of the second operand DECIMAL (10,3).

Let p, s be the precision and scale of the result.

s = max(s1, s2)
s = max (1,3)

Scale of result = 3

p = max(p1 - s1, p2 - s2) + s + 1
p = max(10-1,10-3) + 3 + 1

Precision of result = 11

s and p are capped at their maximum values.

Notes on DATE, TIME, and TIMESTAMP

  • 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

DATE:

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

TIME:

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

TIMESTAMP:

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

INTERVAL:

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:445' day(4) to second(3);
EXPR$0 +4544 01:30:46.445