Data Types (SQL)
Data Types in InterSystems SQL
A data type specifies the kind of value that a table column can hold. In InterSystems SQL, you specify the data type when defining a field with CREATE TABLE or ALTER TABLE. You can define either a Data Definition Language (DDL) data type or an InterSystems IRIS data type class. For example:
CREATE TABLE Employees (
FirstName VARCHAR(30),
LastName VARCHAR(30),
StartDate TIMESTAMP)
CREATE TABLE Employees (
FirstName %String(MAXLEN=30),
LastName %String(MAXLEN=30),
StartDate %TimeStamp)
View Data Type Mappings to InterSystems IRIS
Each DDL data type maps to an equivalent InterSystems IRIS data type. To view the standard mappings for your system:
-
From the Management Portal, select System Administration.
-
Under Configuration and then SQL and Object Settings, click System DDL Mappings.
The System-defined DDL Mappings page shows a table with these columns:
-
Name — The name of a DDL data type that you can specify. DDL data type names are case insensitive.
-
Datatype — The name of the InterSystems IRIS class data type that the DDL data type maps to. Class names are case sensitive.
From the System-defined DDL Mappings page, you can modify and delete existing data types. This table explains the mappings, including the literal data type parameters such as %1 and function parameters such as $$maxval^%apiSQL(%1,%2). For more details about these parameters, see Data Type Mapping Parameters.
DDL Data Type | Corresponding InterSystems IRIS Data Type Class |
---|---|
BIGINT | |
BIGINT(%1) |
%Library.BigIntOpens in a new tab The %1 parameter is ignored and is provided for MySQL compatibility. This data type is equivalent to BIGINT. |
BINARY |
%Library.BinaryOpens in a new tab(MAXLEN=1) |
BINARY VARYING |
%Library.BinaryOpens in a new tab(MAXLEN=1) |
BINARY VARYING(%1) |
%Library.BinaryOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
BINARY(%1) |
%Library.BinaryOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
BIT | %Library.BooleanOpens in a new tab
For more details on this data type, see BIT Data Type. |
BLOB | %Stream.GlobalBinaryOpens in a new tab |
CHAR |
%Library.StringOpens in a new tab(MAXLEN=1) |
CHAR VARYING |
%Library.StringOpens in a new tab(MAXLEN=1) |
CHAR VARYING(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
CHAR(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
CHARACTER |
%Library.BinaryOpens in a new tab(MAXLEN=1) |
CHARACTER VARYING |
%Library.StringOpens in a new tab(MAXLEN=1) |
CHARACTER VARYING(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
CHARACTER(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
CLOB | %Stream.GlobalCharacterOpens in a new tab |
DATE | %Library.DateOpens in a new tab |
DATETIME | %Library.DateTimeOpens in a new tab |
DATETIME2 | %Library.DateTimeOpens in a new tab |
DEC |
%Library.NumericOpens in a new tab(MAXVAL=999999999999999, MINVAL=-999999999999999, SCALE=0) |
DEC(%1) |
%Library.NumericOpens in a new tab(MAXVAL=<|'$$maxval^%apiSQL(%1,0)'|>,MINVAL=<|'$$minval^%apiSQL(%1,0)'|>,SCALE=0) This data type uses function parameters to set MINVAL and MAXVAL based on the input precision parameter (%1) with the scale set to 0. For more details on these parameters, see Precision and Scale. Example: DEC(4) maps to%Library.NumericOpens in a new tab(MAXVAL=9999,MINVAL=–9999,SCALE=0) |
DEC(%1,%2) |
%Library.NumericOpens in a new tab (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2) This data type uses function parameters to set MINVAL, MAXVAL, and SCALE based on the input precision (%1) and scale (%2) parameters. For more details on these parameters, see Precision and Scale. Example: DEC(8,4) maps to %Library.NumericOpens in a new tab(MAXVAL=9999.9999,MINVAL=-9999.9999,SCALE=4) |
DECIMAL |
%Library.NumericOpens in a new tab(MAXVAL=999999999999999, MINVAL=-999999999999999, SCALE=0) |
DECIMAL(%1) |
%Library.NumericOpens in a new tab(MAXVAL=<|'$$maxval^%apiSQL(%1,0)'|>,MINVAL=<|'$$minval^%apiSQL(%1,0)'|>,SCALE=0) This data type uses function parameters to set MINVAL and MAXVAL based on the input precision parameter (%1) with the scale set to 0. For more details on these parameters, see Precision and Scale. This data type is a 64-bit signed integer. Example: DECIMAL(6) maps to %Library.NumericOpens in a new tab(MAXVAL=999999,MINVAL=-999999,SCALE=0) |
DECIMAL(%1,%2) |
%Library.NumericOpens in a new tab (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2) This data type uses function parameters to set MINVAL, MAXVAL, and SCALE based on the input precision (%1) and scale (%2) parameters. For more details on these parameters, see Precision and Scale. Example: DECIMAL(8,4) maps to%Library.NumericOpens in a new tab(MAXVAL=9999.9999,MINVAL=-9999.9999,SCALE=4) |
DOUBLE |
%Library.DoubleOpens in a new tab This is the IEEE floating point standard. An SQL column with this data type returns a default precision of 20. For further details (including important max/min value limits), see the $DOUBLE function. |
DOUBLE PRECISION |
%Library.DoubleOpens in a new tab This is the IEEE floating point standard. An SQL column with this data type returns a default precision of 20. For further details (including important max/min value limits), see the $DOUBLE function. |
FLOAT |
%Library.DoubleOpens in a new tab This is the IEEE floating point standard. An SQL column with this data type returns a default precision of 20. |
FLOAT(%1) |
%Library.DoubleOpens in a new tab This is the IEEE floating point standard. An SQL column with this data type returns a default precision of 20. |
IMAGE | %Stream.GlobalBinaryOpens in a new tab |
INT |
%Library.IntegerOpens in a new tab (MAXVAL=2147483647, MINVAL=-2147483648) |
INT(%1) |
%Library.IntegerOpens in a new tab (MAXVAL=2147483647, MINVAL=-2147483648) The %1 parameter is ignored and is provided for MySQL compatibility. This data type is equivalent to INT. |
INTEGER |
%Library.IntegerOpens in a new tab (MAXVAL=2147483647, MINVAL=-2147483648) |
LONG | %Stream.GlobalCharacterOpens in a new tab |
LONG BINARY | %Stream.GlobalBinaryOpens in a new tab |
LONG RAW | %Stream.GlobalBinaryOpens in a new tab |
LONG VARCHAR | %Stream.GlobalCharacterOpens in a new tab |
LONG VARCHAR(%1) |
%Stream.GlobalCharacterOpens in a new tab The %1 parameter is ignored and is provided for MySQL compatibility. |
LONGTEXT |
%Stream.GlobalCharacterOpens in a new tab The %1 parameter is ignored and is provided for MySQL compatibility. |
LONGVARBINARY | %Stream.GlobalBinaryOpens in a new tab |
LONGVARBINARY(%1) |
%Stream.GlobalBinaryOpens in a new tab The %1 parameter is ignored and is provided for MySQL compatibility. |
LONGVARCHAR | %Stream.GlobalCharacterOpens in a new tab |
LONGVARCHAR(%1) | %Stream.GlobalCharacterOpens in a new tab |
MEDIUMINT |
%Library.IntegerOpens in a new tab(MAXVAL=8388607,MINVAL=-8388608) This data type is provided for MySQL compatibility. |
MEDIUMINT(%1) |
%Library.IntegerOpens in a new tab(MAXVAL=8388607,MINVAL=-8388608) The %1 parameter is ignored and is provided for MySQL compatibility. |
MEDIUMTEXT | %Stream.GlobalCharacterOpens in a new tab |
MONEY | %Library.CurrencyOpens in a new tab |
NATIONAL CHAR | %Library.StringOpens in a new tab(MAXLEN=1) |
NATIONAL CHAR VARYING | %Library.StringOpens in a new tab(MAXLEN=1) |
NATIONAL CHAR VARYING(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
NATIONAL CHAR(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
NATIONAL CHARACTER | %Library.StringOpens in a new tab(MAXLEN=1) |
NATIONAL CHARACTER VARYING | %Library.StringOpens in a new tab(MAXLEN=1) |
NATIONAL CHARACTER VARYING(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
NATIONAL CHARACTER(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
NATIONAL VARCHAR | %Library.StringOpens in a new tab(MAXLEN=1) |
NATIONAL VARCHAR(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
NCHAR |
%Library.StringOpens in a new tab(MAXLEN=1) |
NCHAR(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
NTEXT | %Stream.GlobalCharacterOpens in a new tab |
NUMBER |
%Library.NumericOpens in a new tab(SCALE=0) This data type is a 64-bit signed integer. |
NUMBER(%1) |
%Library.NumericOpens in a new tab(MAXVAL=<|'$$maxval^%apiSQL(%1)'|>,MINVAL=<|'$$minval^%apiSQL(%1)'|>,SCALE=0) This data type uses function parameters to set MINVAL and MAXVAL based on the input precision parameter (%1) with the scale set to 0. This data type is a 64-bit signed integer. Example: NUMBER(6) maps to %Library.NumericOpens in a new tab(MAXVAL=999999,MINVAL=-999999,SCALE=0) |
NUMBER(%1,%2) |
%Library.NumericOpens in a new tab (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2) This data type uses function parameters to set MINVAL, MAXVAL, and SCALE based on the input precision (%1) and scale (%2) parameters. Example: NUMBER(8,4) maps to%Library.NumericOpens in a new tab(MAXVAL=9999.9999,MINVAL=-9999.9999,SCALE=4) |
NUMERIC |
%Library.NumericOpens in a new tab(MAXVAL=999999999999999, MINVAL=-999999999999999, SCALE=0) |
NUMERIC(%1) |
%Library.NumericOpens in a new tab(MAXVAL=<|'$$maxval^%apiSQL(%1,0)'|>,MINVAL=<|'$$minval^%apiSQL(%1,0)'|>,SCALE=0) This data type uses function parameters to set MINVAL and MAXVAL based on the input precision parameter (%1) with the scale set to 0. This data type is a 64-bit signed integer. Example: NUMERIC(6) maps to %Library.NumericOpens in a new tab(MAXVAL=999999,MINVAL=-999999,SCALE=0) |
NUMERIC(%1,%2) |
%Library.NumericOpens in a new tab (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2) This data type uses function parameters to set MINVAL, MAXVAL, and SCALE based on the input precision (%1) and scale (%2) parameters. Example: NUMERIC(8,4) maps to%Library.NumericOpens in a new tab(MAXVAL=9999.9999,MINVAL=-9999.9999,SCALE=4) |
NVARCHAR | %Library.StringOpens in a new tab(MAXLEN=1) |
NVARCHAR(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
NVARCHAR(%1,%2) | %Library.StringOpens in a new tab(MAXLEN=%1) |
NVARCHAR(MAX) |
%Stream.GlobalCharacterOpens in a new tab This data type is equivalent to LONGVARCHAR and is provided for TSQL compatibility. |
POSIXTIME | %Library.PosixTimeOpens in a new tab |
RAW(%1) |
%Library.BinaryOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
REAL |
%Library.DoubleOpens in a new tab This is the IEEE floating point standard. An SQL column with this data type returns a default precision of 20. |
ROWVERSION |
%Library.RowVersionOpens in a new tab This data type is a system-assigned sequential integer. See ROWVERSION Data Type for details. |
SERIAL |
%Library.CounterOpens in a new tab This data type is system-generated |
SMALLDATETIME | %Library.DateTimeOpens in a new tab(MINVAL="1900-01-01 00:00:00",MAXVAL="2079-06-06 23:59:59") |
SMALLINT | %Library.SmallIntOpens in a new tab |
SMALLINT(%1) |
%Library.SmallIntOpens in a new tab The %1 parameter is ignored and is provided for MySQL compatibility. This data type is equivalent to SMALLINT. |
SMALLMONEY | %Library.CurrencyOpens in a new tab |
SYSNAME | %Library.StringOpens in a new tab(MAXLEN=128) |
TEXT | %Stream.GlobalCharacterOpens in a new tab |
TIME | %Library.TimeOpens in a new tab |
TIME(%1) |
%Library.TimeOpens in a new tab(PRECISION=%1) PRECISION is the number of fractional second digits, an integer value in the range 0 through 9. |
TIMESTAMP | %Library.PosixTimeOpens in a new tab |
TIMESTAMP2 | %Library.TimeStampOpens in a new tab |
TINYINT | %Library.TinyIntOpens in a new tab |
TINYINT(%1) |
%Library.TinyIntOpens in a new tab The %1 parameter is ignored and is provided for MySQL compatibility. This data type is equivalent to TINYINT. |
UNIQUEIDENTIFIER | %Library.UniqueIdentifierOpens in a new tab |
VARBINARY | %Library.BinaryOpens in a new tab(MAXLEN=1) |
VARBINARY(%1) |
%Library.BinaryOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
VARCHAR | %Library.StringOpens in a new tab(MAXLEN=1) |
VARCHAR(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
VARCHAR(%1,%2) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
VARCHAR(MAX) |
%Stream.GlobalCharacterOpens in a new tab This data type is equivalent to LONGVARCHAR and is provided for TSQL compatibility. |
VARCHAR2(%1) |
%Library.StringOpens in a new tab(MAXLEN=%1) %1 sets the maximum length of the data type. |
VECTOR | |
EMBEDDING |
Data Type Mapping Parameters
The System-defined DDL Mappings table often includes multiple entries for the same data type to show the different parameters you can specify for that data type. The mapping table also shows parameter default values. Data type classes commonly provide additional parameters to define allowed data values than the DDL data types. You can specify either literal parameters or function parameters. You can also define additional data type class parameters.
Literal Parameters
Literal parameters are identified in the DDL data type and the InterSystems IRIS data type in the format %n, where n is the number of the data type argument. For example, VARCHAR(%1) maps to %String(MAXLEN=%1)
Common literal parameters include maximum string length, minimum and maximum values, and precision and scale values.
Maximum Length
In data type classes, the MAXLEN parameter specifies the maximum length of string data types. DDL types often define these values in a corresponding unnamed parameter.
In this field definition, the data type is a string with a maximum length of 64 characters.
ProductName VARCHAR(64)
ProductName %String(MAXLEN=64)
When specifying this parameter, keep these points in mind:
-
A field with no MAXLEN value can take a value of any length, up to the maximum string length. To define a string field of maximum length, specify VARCHAR(''), which create a property with data type %Library.String(MAXLEN=""). VARCHAR() creates a property with data type %Library.String(MAXLEN=1). To define a binary field with no MAXLEN value, specify VARBINARY(''), which create a property with data type %Library.Binary(MAXLEN=""). VARBINARY() creates a property with data type %Library.Binary(MAXLEN=1).
-
Large MAXLEN: ODBC applications may be affected by an overly large MAXLEN value. ODBC applications try to make decisions about the size of a field needed based on metadata from the server, so the application may allocate more buffer space than is actually needed. For this reason, InterSystems IRIS supplies a system-wide default ODBC VARCHAR maximum length of 4096; this system-wide default is configurable using the Management Portal: from System Administration, select Configuration, then SQL and Object Settings, then SQL. View or set the Default length for VARCHAR option. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab. The InterSystems ODBC driver takes the data from the TCP buffer and converts it into the applications buffer, so MAXLEN size does not affect our ODBC client.
Maximum and Minimum Values
In data type classes, the MINVAL and MAXVAL parameters specify the minimum and maximum values of numeric data types. Data type classes often define these values using function parameters instead of literal parameters. DDL types do not have equivalent parameters.
In this field definition, the data type is an integer from 0 to 100.
Capacity %Integer(MINVAL=0,MAXVAL=100)
Precision and Scale
In data type classes, the PRECISION and SCALE parameters are integer values specifying the precision (maximum number of digits) and scale (maximum number of decimal digits) of numeric data types. Data type classes often define these values as function parameters instead of literal parameters. DDL data types such as NUMERIC often specify precision and scale together as unnamed parameters.
This field definition defines a number that has a precision of 6 and a scale of 2.
UnitPrice NUMERIC(6,2) // Range: -9999.99 to 9999.99
The precision and scale parameters define numeric data types as follows:
-
Precision — The maximum and minimum permitted value, specified as an integer from 0 to 19 + s, where s is the scale. Precision is commonly the total number of digits in the number, but its exact value is determined by the %Library class data type mapping. The maximum integer value is 9223372036854775807. A precision larger than 19 + s defaults to 19 + s.
-
Scale — The maximum number of decimal (fractional) digits permitted, specified as an integer. If s is larger than or equal to the precision, p, only a fractional value is permitted, and the actual p value is ignored. The largest permitted scale is 18, which corresponds to .999999999999999999. A scale larger than 18 defaults to 18.
For information about numeric formatting, refer to the $FNUMBER function.
Function Parameters
Function parameters are used when a parameter in the DDL data type parameter must be transformed before it can be put into the InterSystems IRIS data type. An example of this is the transformation of a DDL data type’s numeric precision and scale parameters into an InterSystems IRIS data type’s MAXVAL, MINVAL parameters.
For example, consider the mapping between the DECIMAL DDL data type and the %NumericOpens in a new tab class as it appears in the System-defined DDL Mappings table in the Management Portal.
DECIMAL(%1,%2)
%Numeric(MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>,MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>,SCALE=%2)
The %1 and %2 parameters specify the precision and scale of numbers in that data type, respectively. For example, a field of type DECIMAL(4,2) stores numbers using a precision of 4 and a scale of 2. InterSystems SQL uses these parameters to derive the minimum value (–99.99) and maximum value (99.99) accepted by the field.
For the %NumericOpens in a new tab class, InterSystems IRIS set the SCALE parameter (SCALE=%2) but it does not have a PRECISION parameter to set. Instead, InterSystems IRIS sets the MAXVAL and MINVAL parameters using these transformation functions:
-
maxval^%apiSQL(precision,scale) returns the maximum valid numeric value, MAXVAL, given the precision and scale.
-
minval^%apiSQL(precision,scale) returns the minimum valid numeric value, MINVAL, given the precision and scale.
The syntax for these transformation functions is as follows:
dataTypeClass(param=<|'func'|>, param2=<|func2|>, ...)
-
dataTypeClass — Name of the data type class being mapped to. Example: %Numeric
-
param — Name of the data type class parameter being set. Example: MAXVAL
-
func — The function call used to set the parameter. Example: maxval^%apiSQL(%1,%2)
The <|'func'|> expression signals the DDL processor to replace the parameters within func using the supplied values and then call the function with those values supplied. The <|'func'|> expression is then replaced with the value returned from the function call.
Additional Parameters
A data type class may define additional data definition parameters that cannot be defined using a DDL data type. These include data validation operations such as an enumerated list of permitted data values, pattern matching of permitted data values, and automatic truncation of data values that exceed the MAXLEN maximum length.
Create New DDL Data Types
You can modify the set of data types either by overriding the data type mapping for a system data type parameter value, or by defining a new user data type. You can modify system data types to override the InterSystems default mappings. You can create user-defined data types to provide additional data type mappings that InterSystems does not supply.
To create a new DDL data type and its mapping:
-
From the Management Portal, select System Administration.
-
Under Configuration and then SQL and Object Settings, click User-defined DDL Mappings.
-
Click Create New User-defined DDL Mapping to open a form for entering your data type.
-
In the Name field of the form, enter a DDL data type specification. For example: VARCHAR(100).
-
In the Datatype field, enter the name of an existing InterSystems IRIS data type class or one that you created. For example: MyString100(MAXLEN=100).
-
Click Save.
The User-defined DDL Mappings table displays the new entry. From this table, you can modify or delete the entry.
You can create a user-defined data type as a data type class. For example, you might wish to create a string data type that takes up to 10 characters and then truncates the rest of the input data. You would create this data type Sample.TruncStr, as follows:
Class Sample.TruncStr Extends %Library.String
{
Parameter MAXLEN=10;
Parameter TRUNCATE=1;
}
To use this data type in a table definition, specify the data type class name:
CREATE TABLE Sample.ShortNames (Name Sample.TruncStr)
When creating data type classes, keep these points in mind:
-
To set parameters such as MINVAL and MAXVAL in your functions, you can use function parameters.
-
If you need to map a DDL data type to an InterSystems IRIS property with a collection type of Stream, specify %Stream.GlobalCharacter for Character Stream data and %Stream.GlobalBinary for Binary Stream data.
-
If DDL encounters a data type not in the DDL data type column of the SystemDataTypes table, it next examines the UserDataTypes table. If no mapping appears for the data type in either table, no conversion of the data type occurs, and the data type passes directly to the class definition as specified in DDL.
For example, the following field definitions could appear in a DDL statement:
CREATE TABLE TestTable ( Field1 %String, Field2 %String(MAXLEN=45) )
Given the above definitions, if DDL finds no mappings for %String or %String(MAXLEN=%1) or %String(MAXLEN=45) in SystemDataTypes or UserDataTypes, then the %String and %String(MAXLEN=45) types are passed directly to the appropriate class definition.
Work With Specific Data Types
Date, Time, and Timestamp Data Types
Using standard InterSystems SQL date, time functions, you can define date, time, and timestamp data types. You can also convert between dates and timestamps. For example, you can use CURRENT_DATE or CURRENT_TIMESTAMP as input to a field defined with that data type, or use DATEADD, DATEDIFF, DATENAME, or DATEPART to manipulate date values stored with this data type.
This table shows how date, time, and timestamp data type classes map to SQL types. The data type classes use this type when performing calculations in SQL. When creating a custom data type class, you can use these mappings to determine which SQL type to specify in the SqlCategory keyword of your class definition. For example:
Class MyApp.MyDateDT [ ClassType = DataType, SQLCategory = DATE ]
{
// class members
}
Data Type Class | Corresponding SQL Type | Notes |
---|---|---|
|
DATE |
By default, the DATE and the corresponding %Library.DateOpens in a new tab data types accept only positive integers, with 0 representing 1840-12-31. To support dates earlier than 1840-12-31 you must define a date field in the table with data type %Library.Date(MINVAL=-nnn), where the MINVAL is a negative number of days counting backwards from 1840-12-31 to a maximum of -672045 (0001-01-01). %Library.DateOpens in a new tab can store a date value as an unsigned or negative integer in the range -672045 to 2980013. Date values can be input as follows:
|
|
TIME |
%Library.TimeOpens in a new tab stores a time value as an unsigned integer in the range 0 through 86399 (a count of seconds since midnight). Time values can be input as follows:
TIME supports fractional seconds, so this data type can also be used for HH:MI:SS.FF to a user-specified number of fractional digits of precision (F), up to a maximum of 9. To support fractional seconds set the PRECISION parameter. For example, TIME(0) (%Time(PRECISION=0)) rounds to the nearest second; TIME(2) (%Time(PRECISION=2)) rounds (or zero-fills) to two fractional digits of precision. If the supplied data also specifies a precision (for example, CURRENT_TIME(3)), the fractional digits stored are as follows:
SQL metadata reports fractional digits of time precision as “scale”; it uses the word “precision” for the overall length of the data. A field using the TIME data type reports precision and scale metadata as follows: TIME(0) (%Time(PRECISION=0)) has a metadata precision of 8 (nn:nn:nn) and a scale of 0. TIME(2) (%Time(PRECISION=2)) has a metadata precision of 11 (nn:nn:nn.ff) and a scale of 2. TIME (%Time or %Time(PRECISION="") take their fractional seconds of precision from the supplied data, and therefore have a metadata precision of 18 and an undefined scale. For details on returning data type, precision and scale metadata, refer to Select-item Metadata. |
|
TIMESTAMP |
%Library.TimeStampOpens in a new tab derives its maximum precision from the system platform’s precision, up to a maximum of 9 fractional second digits, while %Library.PosixTimeOpens in a new tab has a maximum precision of 6 digits. Therefore, %Library.TimeStampOpens in a new tab may be more precise than %Library.PosixTimeOpens in a new tab on some platforms. %Library.TimeStampOpens in a new tab normalization automatically truncates input values with more than 9 digits of precision to 9 fractional second digits.
Note:
%Library.DateTimeOpens in a new tab is a subclass of %Library.TimeStampOpens in a new tab. It defines a type parameter named DATEFORMAT and it overrides the DisplayToLogical() and OdbcToLogical() methods to handle imprecise datetime input that TSQL applications are accustomed to. |
|
MVDATE |
This data type is supported only for MultiValue compatbility. |
|
DATE |
When defining this class, define a LogicalToDate() method to convert logical date values to %Library.DateOpens in a new tab logical values, and a DateToLogical() method that performs the reverse operation. |
|
POSIXTIME |
%PosixTime is an encoded timestamp calculated from the number of seconds (and fractional seconds) since 1970–01–01 00:00:00. Timestamps after that date are represented by a positive %PosixTime value, timestamps before that date are represented by a negative %PosixTime value. %PosixTime supports a maximum of 6 digits of precision for fractional seconds. The earliest date supported by %PosixTime is 0001-01-01 00:00:00, which has a logical value of -6979664624441081856. The last date supported is 9999-12-31 23:59:59.999999, which has a logical value of 1406323805406846975. Because a %PosixTime value is always represented by a encoded 64-bit integer, it can always be unambiguously differentiated from a %Date or %TimeStamp value. For example, the %PosixTime value for 1970–01–01 00:00:00 is 1152921504606846976, the %PosixTime value for 2017–01–01 00:00:00 is 1154404733406846976, and the %PosixTime value for 1969–12–01 00:00:00 is -6917531706041081856. %PosixTime is preferable to %TimeStamp, because it takes up less disk space and memory than the %TimeStamp data type and provides better performance than %TimeStamp. You can integrate %PosixTime and %TimeStamp values by using the ODBC display mode:
You can convert %TimeStamp values to %PosixTime using the TO_POSIXTIME function or the TOPOSIXTIME()Opens in a new tab method. You can use the IsValid()Opens in a new tab method to determine if a numeric value is a valid %PosixTime value. |
Any time data type that does not fit into any of the preceding logical values |
TIME |
When creating this class, define a LogicalToTime() method to convert logical time values to %Library.TimeOpens in a new tab logical values, and a TimeToLogical() method that performs the reverse operation. |
Any timestamp data type that does not fit into any of the preceding logical values |
TIMESTAMP |
When defining this class, define a LogicalToTimeStamp() method to convert logical timestamp values to %Library.TimeStampOpens in a new tab logical values, and a TimeStampToLogical() method that performs the reverse operation. |
You can compare POSIXTIME to DATE or TIMESTAMP values using =, <>, >, or < operators. Refer to Overview of Predicates for further details.
When performing an operation, such as a function or a concatenation, on a date, keep in mind that the SelectMode is applied after the operation is complete. In particular, this means that functions or concatenations are applied to the Logical format that the date is stored in.
When comparing FMTIMESTAMP category values with DATE category values, InterSystems IRIS does not strip the time from the FMTIMESTAMP value before comparing it to the DATE. This is identical behavior to comparing TIMESTAMP with DATE values, and comparing TIMESTAMP with MVDATE values. It is also compatible with how other SQL vendors compare timestamps and dates. This means a comparison of a FMTIMESTAMP 320110202.12 and DATE 62124 are equal when compared using the SQL equality (=) operator. Applications must convert the FMTIMESTAMP value to a DATE or FMDATE value to compare only the date portions of the values.
Dates Prior to December 31, 1840
A date is commonly represented by the DATE data type or the TIMESTAMP data type.
The DATE data type stores a date in $HOROLOG format, as a positive integer count of days from the arbitrary starting date of December 31, 1840. By default, dates can only be represented by a positive integer (MINVAL=0), which corresponds to the date December 31, 1840. However, you can change the %Library.DateOpens in a new tab MINVAL type parameter to enable storage of dates prior to December 31, 1840. By setting MINVAL to a negative number, you can store dates prior to December 31, 1840 as negative integers. The earliest allowed MINVAL value is -672045. This corresponds to January 1 of Year 1 (CE). DATE data type cannot represent BCE (also known as BC) dates.
The TIMESTAMP data type defaults to 1840–12–31 00:00:00 as the earliest allowed timestamp. However, you can change the MINVAL parameter to define a field or property that can store dates prior to December 31, 1840. For example, MyTS %Library.TimeStamp(MINVAL='1492-01-01 00:00:00'). The earliest allowed MINVAL value is 0001–01–01 00:00:00. This corresponds to January 1 of Year 1 (CE). The %TimeStamp data type cannot represent BCE (also known as BC) dates.
Be aware that these date counts do not take into account changes in date caused by the Gregorian calendar reform (enacted October 15, 1582, but not adopted in Britain and its colonies until 1752).
You can redefine the minimum date for your locale as follows:
SET oldMinDate = ##class(%SYS.NLS.Format).GetFormatItem("DATEMINIMUM")
IF oldMinDate=0 {
DO ##class(%SYS.NLS.Format).SetFormatItem("DATEMINIMUM",-672045)
SET newMinDate = ##class(%SYS.NLS.Format).GetFormatItem("DATEMINIMUM")
WRITE "Changed earliest date to ",newMinDate
}
ELSE { WRITE "Earliest date was already reset to ",oldMinDate}
The above example sets the MINVAL for your locale to the earliest permitted date (1/1/01). For more details on configuring dates based on your locale, see Configuring National Language Support (NLS).
InterSystems IRIS does not support using Julian dates with negative logical DATE values (%Library.Date values with MINVAL<0). Thus, these MINVAL<0 values are not compatible with the Julian date format returned by the TO_CHAR function.
Strings
InterSystems IRIS permits a fixed amount of memory to handle strings, so that there is a string length limit. Commonly, extremely long strings should be assigned one of the %Stream.GlobalCharacterOpens in a new tab data types.
No string length limit is enforced over a database driver connection. If the InterSystems IRIS instance and the ODBC driver facilities support different protocols, the lower of the two protocols is used. The protocol that was actually used is recorded in the InterSystems ODBC log.
Note that, by default, InterSystems IRIS establishes a system-wide ODBC VARCHAR maximum length of 4096; this ODBC maximum length is configurable.
List Structures
InterSystems IRIS supports the list structure data type %List (data type class %Library.List). This is a compressed binary format, which does not map to a corresponding native data type for InterSystems SQL. In its internal representation it corresponds to data type VARBINARY with a default MAXLEN of 32749. InterSystems IRIS supports the list structure data type %ListOfBinary (data type class %Library.ListOfBinary) corresponds to data type VARBINARY with a default MAXLEN of 4096.
For this reason, Dynamic SQL cannot use %List data in a WHERE clause comparison. You also cannot use INSERT or UPDATE to set a property value of type %List.
Dynamic SQL returns the data type of list structured data as VARCHAR. To determine if a field in a query is of data type %List or %ListOfBinary you can use the select-item columns metadata isList boolean flag. The CType (client data type) integer code for these data types is 6.
If you use an ODBC or JDBC client, %List data is projected to VARCHAR string data, using LogicalToOdbc conversion. A list is projected as a string with its elements delimited by commas. Data of this type can be used in a WHERE clause, and in INSERT and UPDATE statements. Note that, by default, InterSystems IRIS establishes a system-wide ODBC VARCHAR maximum length of 4096; this ODBC maximum length is configurable.
Also see %Library.ListOpens in a new tab for information on that class. For further details on using lists in a WHERE clause, see the %INLIST predicate and the FOR SOME %ELEMENT predicate. For further details on handling list data as a string, see the %EXTERNAL function.
InterSystems SQL supports eight list functions: $LIST, $LISTBUILD, $LISTDATA, $LISTFIND, $LISTFROMSTRING, $LISTGET, $LISTLENGTH, and $LISTTOSTRING. ObjectScript supports three additional list functions: $LISTVALID to determine if an expression is a list, $LISTSAME to compare two lists, and $LISTNEXT to sequentially retrieve elements from a list.
BIT Data Type
The BIT (%Library.BooleanOpens in a new tab) data type accepts 0, 1, and NULL as valid values.
-
In Logical and ODBC modes the only accepted values are 0, 1, and NULL.
-
In Display mode the DisplayToLogical method first translates a non-null input value to 0 or 1, as follows:
-
Non-zero numbers or numeric strings = 1. For example, 3, '0.1', '-1', '7dwarves'.
-
Non-numeric strings = 0. For example, 'true' or 'false'.
-
Empty string = 0. For example, ''.
-
Stream Data Types
The Stream data types correspond to the InterSystems IRIS class property data types %Stream.GlobalCharacterOpens in a new tab (for CLOBs) and %Stream.GlobalBinaryOpens in a new tab (for BLOBs). These data type classes can define a stream field with a specified LOCATION parameter, or omit this parameter and default to a system-defined storage location.
A field with a Stream data type cannot be used as an argument to most SQL scalar, aggregate, or unary functions. Attempting to do so generates an SQLCODE -37 error code. The few functions that are exceptions are listed in Storing and Using Stream Data (BLOBs and CLOBs).
A field with a Stream data type cannot be used as an argument to most SQL predicate conditions. Attempting to do so generates an SQLCODE -313 error code. The predicates that accept a stream field are listed in Storing and Using Stream Data (BLOBs and CLOBs).
The use of Stream data types in indexes, and when performing inserts and updates are also restricted. For further details on Stream restrictions, refer to Storing and Using Stream Data (BLOBs and CLOBs).
SERIAL Data Type
A field with a SERIAL (%Library.CounterOpens in a new tab) data type can take a user-specified positive integer value, or InterSystems IRIS can assign it a sequential positive integer value. %Library.CounterOpens in a new tab extends %Library.BigIntOpens in a new tab.
An INSERT operation specifies one of the following values for a SERIAL field:
-
No value, 0 (zero), or a nonnumeric value: InterSystems IRIS ignores the specified value, and instead increments this field's current serial counter value by 1, and inserts the resulting integer into the field.
-
A positive integer value: InterSystems IRIS inserts the user-specified value into the field, and changes the serial counter value for this field to this integer value.
Thus a SERIAL field contains a series incremental integer values. These values are not necessarily continuous or unique. For example, the following is a valid series of values for a SERIAL field: 1, 2, 3, 17, 18, 25, 25, 26, 27. Sequential integers are either InterSystems IRIS-generated or user-supplied; nonsequential integers are user-supplied. If you wish SERIAL field values to be unique, you must apply a UNIQUE constraint on the field.
An UPDATE operation has no effect on automatically-assigned SERIAL counter field values. However, an update performed using INSERT OR UPDATE causes a skip in integer sequence for subsequent insert operations for a SERIAL field.
An UPDATE operation can only change a serial field value if the field currently has no value (NULL), or its value is 0. Otherwise, an SQLCODE -105 error is generated.
InterSystems IRIS imposes no restriction on the number of SERIAL fields in a table.
ROWVERSION Data Type
The ROWVERSION data type defines a read-only field that contains a unique system-assigned positive integer, beginning with 1. InterSystems IRIS assigns sequential integers as part of each insert, update, or %Save operation. These values are not user-modifiable.
InterSystems IRIS maintains a single row version counter namespace-wide. All tables in a namespace that contain a ROWVERSION field share the same row version counter. Thus, the ROWVERSION field provides row-level version control, allowing you to determine the order in which changes were made to rows in one or more tables in a namespace.
You can only specify one field of ROWVERSION data type per table.
The ROWVERSION field should not be included in a unique key or primary key. The ROWVERSION field cannot be part of an IDKey index.
For details on using ROWVERSION, refer to RowVersion Field.
ROWVERSION and SERIAL Counters
Both ROWVERSION and SERIAL (%Library.CounterOpens in a new tab) data type fields receive a sequential integer from an internal counter as part of an INSERT operation. But these two counters are significantly different and are used for different purposes:
-
The ROWVERSION counter is at the namespace level. The SERIAL counter is at the table level. These two counters are completely independent of each other and independent of the RowID counter.
-
The ROWVERSION counter is incremented by insert, update, or %Save operations. The SERIAL counter is only incremented by insert operations. An update performed using INSERT OR UPDATE can cause a gap in the SERIAL counter sequence.
-
A ROWVERSION field value cannot be user-specified; the value is always supplied from the ROWVERSION counter. A SERIAL field value is supplied from the table’s internal counter during an insert if you do not specify a value for this field. If an insert supplies a SERIAL integer value, that value is inserted rather than the current counter value:
-
If an insert supplies a SERIAL field value greater than the current internal counter value, InterSystems IRIS inserts that value into the field and resets the internal counter to that value.
-
If an insert supplies a SERIAL field value less than the current counter value, InterSystems IRIS does not reset the internal counter.
-
An insert can supply a SERIAL field value as a negative integer or a fractional number. InterSystems IRIS truncates a fractional number to its integer component. If the supplied SERIAL field value is 0 or NULL, InterSystems IRIS ignores the user-supplied value and inserts the current internal counter value.
You cannot update an existing SERIAL field value.
-
-
A ROWVERSION field value is always unique. Because you can insert a user-specified SERIAL field value, you must specify a UNIQUE field constraint to guarantee unique SERIAL field values.
-
The ROWVERSION counter cannot be reset. A TRUNCATE TABLE resets the SERIAL counter; performing a DELETE on all rows does not reset the SERIAL counter.
-
Only one ROWVERSION field is allowed per table. You can specify multiple SERIAL fields in a table.
DDL Data Types Exposed by InterSystems ODBC / JDBC
InterSystems ODBC exposes a subset of the DDL data types, and maps other data types to this subset of data types. These mappings are not reversible. For example, the statement CREATE TABLE mytable (f1 BINARY) creates an InterSystems IRIS class that is projected to ODBC as mytable (f1 VARBINARY). An InterSystems IRIS list data type is projected to ODBC as a VARCHAR string.
ODBC exposes the following data types: BIGINT, BIT, DATE, DOUBLE, GUID, INTEGER, LONGVARBINARY, LONGVARCHAR, NUMERIC, OREF, POSIXTIME, SMALLINT, TIME, TIMESTAMP, TINYINT, VARBINARY, VARCHAR. Note that, by default, InterSystems IRIS establishes a system-wide ODBC VARCHAR maximum length of 4096; this ODBC maximum length is configurable.
When one of these ODBC/JDBC data type values is mapped to InterSystems SQL, the following operations occur: DOUBLE data is cast using $DOUBLE. NUMERIC data is cast using $DECIMAL.
The GUID data type corresponds to InterSystems SQL UNIQUEIDENTIFIER data type. Failing to specify a valid value to a GUID / UNIQUEIDENTIFIER field generates a #7212 General Error. To generate a GUID value, use the %SYSTEM.Util.CreateGUID()Opens in a new tab method.
VECTOR
InterSystems SQL supports the VECTOR type to store data in a compressed format well suited for particular optimizations. Vectors are typed, and all data in a vector is stored and formatted in the vector’s type. Possible vector types are integer, decimal, double, and float. The default type is float.
There are three constructors for the VECTOR type:
VECTOR(type,length)
VECTOR(type)
VECTOR
When defining a column with the VECTOR type in a CREATE TABLE statement, it is recommended that you specify a vector length in the type constructor. When the length is specified, the system throws an error when a vector with a different length is added to that column, ensuring that all vectors in the column have the same length. This uniformity ensures that vector functions, such as VECTOR_DOT_PRODUCT and VECTOR_COSINE, either execute successfully on all vectors in the column or fail on all vectors in the column.
EMBEDDING
InterSystems SQL supports the EMBEDDING type to transparently convert text into a VECTOR. The EMBEDDING type accepts strings of any length and, in conjunction with automatically generated COMPUTECODE and COMPUTEONCHANGE properties, converts the strings into an embedding and stores them as VECTORs.
There is one constructor for the EMBEDDING type:
EMBEDDING(model,source)
In the above constructor, model is the name of an embedding configuration and source is the name of the column (or columns) that the embedding configuration should use to generate an embedding.
Convert Between Data Types
To convert data from one data type to another, use the CAST or CONVERT function.
CAST supports conversion to several character string and numeric data types, as well as to DATE, TIME, and the TIMESTAMP and POSIXTIME timestamp data types.
CONVERT has two syntactical forms. Both forms support conversion to and from DATE, TIME, and the TIMESTAMP and POSIXTIME timestamp data types, as well as conversion between other data types.
When you CAST or CONVERT a value to VARCHAR, the default size mapping is 30 characters, even though VARCHAR with no specified size maps to a MAXLEN of 1. This default size of 30 characters is provided for compatibility with non-InterSystems IRIS software requirements.
Data Type Precedence
When an operation can return several different values, and these values may have different data types, InterSystems IRIS assigns the return value whichever data type has the highest precedence. For example, a NUMERIC data type can contain all possible INTEGER data type values, but an INTEGER data type cannot contain all possible NUMERIC data type values. Thus NUMERIC has the higher precedence (is more inclusive).
For example, if a CASE statement has a possible result value of data type INTEGER, and a possible result value of data type NUMERIC, the actual result is always of type NUMERIC, regardless of which of these two cases are taken.
The precedence for data types is as follows, from highest (most inclusive) to lowest:
LONGVARBINARY LONGVARCHAR VARBINARY VARCHAR GUID TIMESTAMP DOUBLE NUMERIC BIGINT INTEGER DATE TIME SMALLINT TINYINT BIT
Data Type Normalization and Validation
The %Library.DataTypeOpens in a new tab superclass has subclasses for the specific data types. These data type classes provide a Normalize() method to normalize an input value to the data type format and an IsValid() method to determine if an input value is valid for that data type, as well as various mode conversion methods such as LogicalToDisplay() and DisplayToLogical().
The following examples show the Normalize()Opens in a new tab method for the %TimeStamp data type:
SET indate=64701
SET tsdate=##class(%Library.TimeStamp).Normalize(indate)
WRITE "%TimeStamp date: ",tsdate
SET indate="2018-2-22"
SET tsdate=##class(%Library.TimeStamp).Normalize(indate)
WRITE "%TimeStamp date: ",tsdate
The following examples show the IsValid()Opens in a new tab method for the %TimeStamp data type:
SET datestr="July 4, 2018"
SET stat=##class(%Library.TimeStamp).IsValid(datestr)
IF stat=1 {WRITE datestr," is a valid %TimeStamp",! }
ELSE {WRITE datestr," is not a valid %TimeStamp",!}
SET leapdate="2016-02-29 00:00:00"
SET noleap="2018-02-29 00:00:00"
SET stat=##class(%Library.TimeStamp).IsValid(leapdate)
IF stat=1 {WRITE leapdate," is a valid %TimeStamp",! }
ELSE {WRITE leapdate," is not a valid %TimeStamp",!}
SET stat=##class(%Library.TimeStamp).IsValid(noleap)
IF stat=1 {WRITE noleap," is a valid %TimeStamp",! }
ELSE {WRITE noleap," is not a valid %TimeStamp",!}
Returning Data Types Using Query Metadata
You can use Dynamic SQL to return metadata about a query, including the data type of a specified column in the query.
The following Dynamic SQL examples return the column name and the integer code for the ODBC data type for each of the columns in Sample.Person and Sample.Employee:
SET myquery="SELECT * FROM Sample.Person"
SET tStatement=##class(%SQL.Statement).%New()
SET tStatus=tStatement.%Prepare(myquery)
SET x=tStatement.%Metadata.columnCount
WHILE x>0 {
SET column=tStatement.%Metadata.columns.GetAt(x)
WRITE !,x," ",column.colName," ",column.ODBCType
SET x=x-1 }
WRITE !,"end of columns"
SET myquery="SELECT * FROM Sample.Employee"
SET tStatement=##class(%SQL.Statement).%New()
SET tStatus=tStatement.%Prepare(myquery)
SET x=tStatement.%Metadata.columnCount
WHILE x>0 {
SET column=tStatement.%Metadata.columns.GetAt(x)
WRITE !,x," ",column.colName," ",column.ODBCType
SET x=x-1 }
WRITE !,"end of columns"
List structured data, such as the FavoriteColors column in Sample.Person, returns a data type of 12 (VARCHAR) because ODBC represents an ObjectScript %List data type value as a string of comma-separated values.
Steams data, such as the Notes and Picture columns in Sample.Employee, return the data types -1 (LONGVARCHAR) or -4 (LONGVARBINARY).
A ROWVERSION field returns data type -5 because %Library.RowVersion is a subclass of %Library.BigInt.
For further details, refer to Dynamic SQL and see %SQL.StatementOpens in a new tab.
Integer Codes for Data Types
In query metadata and other contexts, the defined data type for a column may be returned as an integer code. The CType (client data type) integer codes are listed in the %SQL.StatementColumnOpens in a new tab clientTypeOpens in a new tab property. For further details, refer to Select-item Metadata.
SQLType data type codes are used by ODBC and JDBC. ODBC data type codes are returned by %SQL.Statement.%Metadata.columns.GetAt() method, as shown in the example above. SQL Shell metadata also returns ODBC data type codes. The JDBC codes are the same as the ODBC codes, except in the representation of time and date data types. These ODBC and JDBC values are listed below:
ODBC | JDBC | Data Type |
-11 | -11 | GUID |
-7 | -7 | BIT |
-6 | -6 | TINYINT |
-5 | -5 | BIGINT |
-4 | -4 | LONGVARBINARY |
-3 | -3 | VARBINARY |
-2 | -2 | BINARY |
-1 | -1 | LONGVARCHAR |
0 | 0 | Unknown type |
1 | 1 | CHAR |
2 | 2 | NUMERIC |
3 | 3 | DECIMAL |
4 | 4 | INTEGER |
5 | 5 | SMALLINT |
6 | 6 | FLOAT |
7 | 7 | REAL |
8 | 8 | DOUBLE |
9 | 91 | DATE |
10 | 92 | TIME |
11 | 93 | TIMESTAMP |
12 | 12 | VARCHAR |
For further details, refer to Dynamic SQL.
InterSystems IRIS also supports Unicode SQL types for ODBC applications working with multibyte character sets, such as in Chinese, Hebrew, Japanese, or Korean locales.
ODBC | Data Type |
-10 | WLONGVARCHAR |
-9 | WVARCHAR |
To activate this functionality, refer to Using an InterSystems Database as an ODBC Data Source on Windows.