DataCaster Data Types
DataCaster provides extensible type support, with built-in support for the data types widely used in databases. Data types in DataCaster implement the
DataType interface, which must be implemented by new data types. New data types can be made available in DataCaster via the data types configuration file, once the data type is implemented.
The following sections list some of the built-in types and some information on syntax and usage.
Number data types
| Data Type | Syntax | Description | Format and/or Range | Example |
|---|
INTEGER | INTEGER or INT | It defines a set of possibly signed whole numbers that have a scale of zero. | -2,147,483,647 to +2,147,483,647 | -5000, 9879299 |
SMALLINT | SMALLINT | It defines a set of possibly signed whole numbers that have a scale of zero. | -32,767 to +32,767 | -50, 9879 |
NUMERIC | { NUMERIC or NUMBER } [(precision [,scale] )] | It defines a set of possibly signed decimal numbers with an optionally defined precision and optionally defined scale. | NUMERIC(1) { Equivalent to NUMERIC } --> -9 to +9 | -5, +9 |
| NUMERIC(4) --> -9999 to +9999 | -50, +999 |
| NUMERIC(5, 2) --> -999.99 to +999.99 | -65.73, +819.3 |
DECIMAL | { DECIMAL or DEC } [(precision [, scale] )] | It defines a set of possibly signed decimal numbers with an optionally defined precision and optionally defined scale. | DEC(1) { Equivalent to DECIMAL } --> -9 to +9 | -5, +9 |
| DEC(4) --> -9999 to +9999 | -50, +999 |
| DEC(5, 2) --> -999.99 to +999.99 | -65.73, +819.3 |
FLOAT | FLOAT [(precision)] | It defines a set of values that are possibly signed floating-point numbers. | [sign]+digit+period+ upto 14 digits +E+[sign]+ upto 3 digits | -1.51263E+020, .2122E-012 |
REAL | REAL | It defines a set of values that are possibly signed floating-point numbers. | [sign]+digit+period+ upto 6 digits +E+[sign]+ upto 2 digits | -1.51263E+20, .2122E-02 |
DOUBLE PRECISION | DOUBLE PRECISION | It defines a set of values that are possibly signed floating-point numbers. | [sign]+digit+period+ upto 14 digits +E+[sign]+ upto 3 digits | -1.51263E+020, .2122E-012 |
Bit String data types
| Data Type | Syntax | Description | Format and/or Range | Example |
|---|
BIT | BIT [(length)] | It defines a set of bit string values that are any correctly sized string of bits.(zero's are padded as per the length specified) | BIT(1) { equivalent to BIT } --> 1 to 4096*8 | 'B1' |
| BIT(8) | 'B11110000', 'X4D' |
BIT VARYING | BIT VARYING [(length)] | It defines a set of bit string values that are any correctly sized string of bits. | BIT VARYING(16) | 'B11110000', 'XA9', 'X4D51' |
Binary String data types
BINARY LARGE OBJECT
It defines a set of binary string values that are any correctly sized sting of octets that are not associated with a Character set.
Syntax: BLOB
Usage: Blobs are used to store large amount of data as raw bytes, mostly used to store binary data like images and data files.
Example: "Content"
Character String data types
| Data Type | Syntax | Description | Format and/or Range | Example |
|---|
CHARACTER | { CHARACTER or CHAR } [(length)] | It defines a set of character string values upto 1KB in length. (White spaces are padded to fill the lenght) | CHAR(1) { equivalent to CHAR } | 'A' |
| CHAR(10) | 'BOB', 'CHARACTERS' |
CHARACTER VARYING | VARCHAR (length) | It defines a set of character string values upto 1KB in length. | VARCHAR(10) | 'BOB', 'CHARACTERS' |
TEXT | TEXT | It defines a set of character string values upto 1 MB in length. | TEXT | 'BOB', 'CHARACTERS' |
CHARACTER LARGE OBJECT | CLOB | It defines a set of large object character string values. | CLOB {Used to store large amount of data in Character format} | "Any length of character string can go in here" |
Temporal data types
DATE TYPE
It defines a set of correctly formed values that represent any valid Gregorian calendar date between '0001-01-01' and '9999-12-31'. It has a length of 10 positions.
| Syntax | Format Supported | Example |
|---|
DATE | (Standard) DATE 'yyyy-MM-dd' | DATE '2005-09-28' |
| 'mm/dd/yyyy' | '01/30/12' |
| 'mm.dd.yyyy' | '06.28.2056' |
| 'mm-dd-yyyy' | '10-10-06' |
| 'Week, Month Date, Year Era' | 'Sunday, April 12, 1992 AD' |
| 'month date, yyyy' | 'Jan 12, 1982' |
TIME TYPE
It defines a set of correctly formed values that represent any valid time of day (based on 24 hour clock) between '00:00:00' and '23:59:61.999999'. It has a length of atleast 8 positions.
| Syntax | Format Supported | Example |
|---|
TIME | (Standard) TIME 'hh:mm:ss' | TIME '16:03:00' |
| 'hh:mm a' | '3:30 PM' |
| 'hh:mm:ss a' | '3:30:32 PM' |
| 'hh:mm:ss a z' | '3:30:42 pm PST' |
TIMESTAMP TYPE
It defines a set of correctly formed values that represent any valid Gregorian calendar date between '0001-01-01' and '9999-12-31' combined with any valid time of day between '00:00:00' and '23:59:61.999999'. It has a length of alteast 19 positions.
| Syntax | Format Supported | Example |
|---|
TIMESTAMP | (Standard) TIMESTAMP 'yyyy-MM-dd hh:mm:ss' | TIMESTAMP '2005-10-03 12:02:30' |
| 'mm/dd/yy hh:mm a' | '07/10/96 8:14 PM' |
| 'mm/dd/yy hh:mm:ss a z' | '07/10/96 4:50:11 PM PDT' |
| 'mm/dd/yy hh:mm:ss a' | '07/10/96 3:50:11 PM' |
| 'month date, yyyy hh:mm a' | 'Nov 4, 2003 8:14 PM' |
| 'month date, yyyy hh:mm:ss a' | 'Nov 4, 2003 3:50:11 PM' |
| 'month date, yyyy hh:mm:ss a z' | 'Nov 4, 2003 4:50:11 PM PDT' |
INTERVAL TYPE
It defines a set of correctly formed values that represent any span of time compatible with the INTERVAL QUALIFIER. It has a length of alteast of 1 position.
General Syntax: INTERVAL interval_qualifier
| Syntax | Format Supported | Example |
|---|
INTERVAL YEAR | INTERVAL 'yyyy' YEAR | INTERVAL '200' YEAR |
INTERVAL MONTH | INTERVAL 'MM' MONTH | INTERVAL '32' MONTH |
INTERVAL DAY | INTERVAL 'dd' DAY | INTERVAL '25' DAY |
INTERVAL HOUR | INTERVAL 'hh' HOUR | INTERVAL '20' HOUR |
INTERVAL MINUTE | INTERVAL 'mm' MINUTE | INTERVAL '50' MINUTE |
INTERVAL SECOND | INTERVAL 'ss' SECOND | INTERVAL '54' SECOND |
| INTERVAL 'ss.nnnnnn' SECOND | INTERVAL '45.999900' SECOND |
INTERVAL YEAR TO MONTH | INTERVAL 'yyyy-MM' YEAR TO MONTH | INTERVAL '5-2' YEAR TO MONTH |
INTERVAL DAY TO HOUR | INTERVAL 'dd hh' DAY TO HOUR (OR) INTERVAL 'dd:hh' DAY TO HOUR | INTERVAL '10:22' DAY TO HOUR |
INTERVAL DAY TO MINUTE | INTERVAL 'dd hh:mm' DAY TO MINUTE | INTERVAL '12 22:50' DAY TO MINUTE |
INTERVAL HOUR TO MINUTE | INTERVAL 'hh:mm' HOUR TO MINUTE | INTERVAL '10:40' HOUR TO MINUTE |
INTERVAL DAY TO SECOND | INTERVAL 'dd hh:mm:ss.nnnnnn' DAY TO SECOND | INTERVAL '4 12:35:42.03' DAY TO SECOND |
INTERVAL HOUR TO SECOND | INTERVAL 'hh:mm:ss.nnnnnn' HOUR TO SECOND | INTERVAL '32:38:49' HOUR TO SECOND |
INTERVAL MINUTE TO SECOND | INTERVAL 'mm:ss' MINUTE TO SECOND | INTERVAL '80:12.555555' MINUTE TO SECOND |
INTERVAL | 'DATE(date_one) TO DATE(date_two)' | 'DATE(01/30/12) TO DATE(11/30/11)' |
| 'TIME(time_one) TO TIME(time_two)' | 'TIME(3:30:32 PM) TO TIME(3:30 AM)' |
| 'TIMESTAMP(timestamp_one) TO TIMESTAMP(timestamp_two)' | 'TIMESTAMP(Nov 4, 2003 8:14 PM) TO TIMESTAMP(07/10/96 4:50:11 PM PDT)' |
| 'yyyy:ww:dd hh:mm:ss' | '1:10:5 12:10:25' |
| 'dd hh:mm:ss' | '5 12:10:25' |
| 'hh:mm:ss' | '12:10:25' |
| 'yyyy:ww:dd DAYS' | '1:10:5 DAYS' |
| 'yyyy YEARS' | '10 YEARS' |
| 'hh HOURS' | '5 HOURS' |
| INTERVAL 'yyyy-MM-dd hh:mm:ss' | INTERVAL '5-0-12 23:42:06' |
Formats Not Supported
| !DataType | Format Not Supported | Example |
|---|
| Date | 'dd-month-yy' | '10-Apr-98' |
| Time | 'hh:mm:ss oclock a' | '1:32:19 oclock PM' |
| 'hh:mm:ss:ms' | '08:15:55:624' |
| TimeStamp | 'Week, Month Date, Year Era hh:mm a' | 'Tuesday, April 12, 1952 AD 8:14 PM' |
| 'Week, Month Date, Year Era hh:mm:ss a' | 'Tuesday, April 12, 1952 AD 3:50:11 PM' |
| 'Week, Month Date, Year Era hh:mm:ss a z' | 'Tuesday, April 12, 1952 BC 4:50:11 PM PDT' |
Boolean data type
BOOLEAN
It defines a set of truth values, either TRUE or FALSE.
Syntax: BOOLEAN
Example values: 'TRUE', 'FALSE'