Informix Guide to SQL: Reference Data Types

Informix database servers support the following built-in data types.

For a description of character, numeric, and miscellaneous data types, refer to the appropriate entry in Description of Data Types. Page references are in the alphabetical list in Figure 2-2 on page 2-6.

The following sections provide additional information on large-object and time data types.

A large object is a data object that is logically stored in a table column but physically stored independently of the column. Large objects are stored separately from the table because they typically store a large amount of data. Separation of this data from the table can increase performance.

Figure 2-10 shows the large-object data types.

Figure 2-10 Large-Object Data Types

Only Dynamic Server supports BLOB and CLOB data types.

For the relative advantages and disadvantages of simple and smart large objects, see the Informix Guide to Database Design and Implementation.

Simple large objects are a category of large objects that have a theoretical limit of 2³¹ bytes and a practical limit that your disk capacity determines. Informix database servers support the following simple-large-object data types:

Unlike smart large objects, simple large objects do not support random access to the data. When you transfer a simple large object between a client application and the database server, you must transfer the entire BYTE or TEXT value. If the data does not fit into memory, you must store it in an operating-system file and then retrieve it from that file.

The database server stores simple large objects in blobspaces. A blobspace is a logical storage area that contains one or more chunks that only store BYTE and TEXT data. For information on how to define blobspaces, see your Administrator's Guide.

Smart large objects are a category of large objects that support random access to the data and are generally recoverable. The random access feature allows you to seek and read through the smart large object as if it were an operating-system file.

Smart large objects are also useful for opaque data types with large storage requirements. (See the description of opaque data types on page 2-62.)

Dynamic Server supports the following smart-large-object data types:

Dynamic Server stores smart large objects in sbspaces. An sbspace is a logical storage area that contains one or more chunks that store only BLOB and CLOB data. For information on how to define sbspaces, see your Performance Guide.

When you define a BLOB or CLOB column, you can determine the following large-object characteristics:

• LOG and NOLOG: whether the database server should log the smart large object in accordance with the current database log mode.
• KEEP ACCESS TIME and NO KEEP ACCESS TIME: whether the database server should keep track of the last time the smart large object was accessed.
• HIGH INTEG and MODERATE INTEG: whether the database server should use page headers to detect data corruption.

Use of these characteristics can affect performance. For information, see your Performance Guide.

When you access a smart-large-object column with an SQL statement, the database server does not send the actual BLOB or CLOB data. Instead, it establishes a pointer to the data and returns this pointer. The client application can then use this pointer to perform the open, read, or write operations on the smart large object.

To access a BLOB or CLOB column from within a client application, use one of the following application programming interfaces (APIs):

• From within an Informix ESQL/C program, use the smart-large-object API.

For more information, see the Informix ESQL/C Programmer's Manual.

• From within a DataBlade module, use the Client and Server API.

For more information, see the DataBlade API Programmer's Manual.

For information on smart large objects, see the Informix Guide to SQL: Syntax and Informix Guide to Database Design and Implementation.

You can use DATE, DATETIME, and INTERVAL data in a variety of arithmetic and relational expressions. You can manipulate a DATETIME value with another DATETIME value, an INTERVAL value, the current time (identified by the keyword CURRENT), or a specified unit of time (identified by the keyword UNITS).

In most situations, you can use a DATE value wherever it is appropriate to use a DATETIME value and vice versa. You also can manipulate an INTERVAL value with the same choices as a DATETIME value. In addition, you can multiply or divide an INTERVAL value by a number.

An INTERVAL column can hold a value that represents the difference between two DATETIME values or the difference between (or sum of) two INTERVAL values. In either case, the result is a span of time, which is an INTERVAL value. On the other hand, if you add or subtract an INTERVAL value from a DATETIME value, another DATETIME value is produced because the result is a specific time.

Figure 2-11 indicates the range of expressions that you can use with DATE, DATETIME, and INTERVAL data and the data type that results from each expression.

No other combinations are allowed. You cannot add two DATETIME values because this operation does not produce either a specific time or a span of time. For example, you cannot add December 25 and January 1, but you can subtract one from the other to find the time span between them.

You can subtract most DATETIME values from each other. Dates can be in any order and the result is either a positive or a negative INTERVAL value. The first DATETIME value determines the field precision of the result.

If the second DATETIME value has fewer fields than the first, the shorter value is extended automatically to match the longer one. (See the discussion of the EXTEND function in the Expression segment in the Informix Guide to SQL: Syntax.)

In the following example, subtracting the DATETIME YEAR TO HOUR value from the DATETIME YEAR TO MINUTE value results in a positive interval value of 60 days, 1 hour, and 30 minutes. Because minutes were not included in the second value, the database server sets the minutes for the result to 0.

DATETIME  (1999-9-30  12:30)  YEAR  TO  MINUTE

	-  DATETIME  (1999-8-1  11)  YEAR  TO  HOUR



Result:  INTERVAL  (60  01:30)  DAY  TO  MINUTE

If the second DATETIME value has more fields than the first (regardless of whether the precision of the extra fields is larger or smaller than those in the first value), the additional fields in the second value are ignored in the calculation.

In the following expression (and result), the year is not included for the second value. Therefore, the year is set automatically to the current year, in this case 1999, and the resulting INTERVAL is negative, which indicates that the second date is later than the first.

DATETIME  (1999-9-30)  YEAR  TO  DAY

	-  DATETIME  (10-1)  MONTH  TO  DAY



Result:  INTERVAL  (1)  DAY  TO  DAY [assuming current year 
is 1999]

INTERVAL values can be added to or subtracted from DATETIME values. In either case, the result is a DATETIME value. If you are adding an INTERVAL value to a DATETIME value, the order of values is unimportant; however, if you are subtracting, the DATETIME value must come first. Adding or subtracting an INTERVAL value simply moves the DATETIME value forward or backward in time. The expression shown in the following example moves the date ahead three years and five months:

DATETIME  (1994-8-1)  YEAR  TO  DAY

	+  INTERVAL  (3-5)  YEAR  TO  MONTH



Result:  DATETIME  (1998-01-01)  YEAR  TO  DAY




Important: Evaluate the logic of your addition or subtraction. Remember that months can be 28, 29, 30, or 31 days and that years can be 365 or 366 days.

In most situations, the database server automatically adjusts the calculation when the initial values do not have the same precision. However, in certain situations, you must explicitly adjust the precision of one value to perform the calculation. If the INTERVAL value you are adding or subtracting has fields that are not included in the DATETIME value, you must use the EXTEND function to explicitly extend the field qualifier of the DATETIME value. (For more information on the EXTEND function, see the Expression segment in the Informix Guide to SQL: Syntax.)

For example, you cannot subtract a minute INTERVAL value from the DATETIME value in the previous example that has a YEAR TO DAY field qualifier. You can, however, use the EXTEND function to perform this calculation, as the following example shows:

EXTEND  (DATETIME  (1998-8-1)  YEAR  TO  DAY,  YEAR  TO  MINUTE)

	-  INTERVAL  (720)  MINUTE(3)  TO  MINUTE



Result:  DATETIME  (1998-07-31  12:00)  YEAR  TO  MINUTE

The EXTEND function allows you to explicitly increase the DATETIME precision from YEAR TO DAY to YEAR TO MINUTE. This allows the database server to perform the calculation, with the resulting extended precision of YEAR TO MINUTE.

You can use DATE values in arithmetic expressions with DATETIME or INTERVAL values by writing expressions that allow the manipulations that Figure 2-12 shows.

In the cases that Figure 2-12 shows, DATE values are first converted to their corresponding DATETIME equivalents, and then the expression is computed normally.

Although you can interchange DATE and DATETIME values in many situations, you must indicate whether a value is a DATE or a DATETIME data type. A DATE value can come from the following sources:

• A column or program variable of type DATE
• The TODAY keyword
• The DATE() function
• The MDY function
• A DATE literal

A DATETIME value can come from the following sources:

• A column or program variable of type DATETIME
• The CURRENT keyword
• The EXTEND function
• A DATETIME literal

The database locale defines the default DATE and DATETIME formats. For the default locale, U.S. English, these formats are 'mm/dd/yy' for DATE values and 'yyyy-mm-dd hh:MM:ss' for DATETIME values.

When you represent DATE and DATETIME values as quoted character strings, the fields in the strings must be in proper order. In other words, when a DATE value is expected, the string must be in DATE format and when a DATETIME value is expected, the string must be in DATETIME format. For example, you can use the string '10/30/1999' as a DATE string but not as a DATETIME string. Instead, you must use '1999-10-30' or '99-10-30' as the DATETIME string.

If you use a nondefault locale, the DATE and DATETIME strings must match the formats that your locale defines. For more information, see the Informix Guide to GLS Functionality.

You can customize the DATE format that the database server expects with the DBDATE and GL_DATE environment variables. You can customize the DATETIME format that the database server expects with the DBTIME and GL_DATETIME environment variables. For more information, see DBDATE and DBTIME. For more information on all these environment variables, see the Informix Guide to GLS Functionality.

You can also subtract one DATE value from another DATE value, but the result is a positive or negative INTEGER value rather than an INTERVAL value. If an INTERVAL value is required, you can either convert the INTEGER value into an INTERVAL value or one of the DATE values into a DATETIME value before subtracting.

For example, the following expression uses the DATE() function to convert character string constants to DATE values, calculates their difference, and then uses the UNITS DAY keywords to convert the INTEGER result into an INTERVAL value:

(DATE ('5/2/1994') - DATE ('4/6/1955')) UNITS DAY

Result: INTERVAL (12810) DAY(5) TO DAY

If you need YEAR TO MONTH precision, you can use the EXTEND function on the first DATE operand, as the following example shows:

EXTEND (DATE ('5/2/1994'), YEAR TO MONTH) - DATE ('4/6/1955')

Result: INTERVAL (39-01) YEAR TO MONTH

The resulting INTERVAL precision is YEAR TO MONTH because the DATETIME value came first. If the DATE value had come first, the resulting INTERVAL precision would have been DAY(5) TO DAY.

You can add or subtract INTERVAL values as long as both values are from the same class; that is, both are year-month or both are day-time. In the following example, a SECOND TO FRACTION value is subtracted from a MINUTE TO FRACTION value:

INTERVAL (100:30.0005) MINUTE(3) TO FRACTION(4)
- INTERVAL (120.01) SECOND(3) TO FRACTION

Result: INTERVAL (98:29.9905) MINUTE TO FRACTION(4)

The use of numeric qualifiers alerts the database server that the MINUTE and FRACTION in the first value and the SECOND in the second value exceed the default number of digits.

When you add or subtract INTERVAL values, the second value cannot have a field with greater precision than the first. The second INTERVAL, however, can have a field of smaller precision than the first. For example, the second INTERVAL can be HOUR TO SECOND when the first is DAY TO HOUR. The additional fields (in this case MINUTE and SECOND) in the second INTERVAL value are ignored in the calculation.

You can multiply or divide INTERVAL values by a number that can be an integer or a fraction. However, any remainder from the calculation is ignored and the result is truncated. The following expression multiplies an INTERVAL by a fraction:

INTERVAL  (15:30.0002)  MINUTE  TO  FRACTION(4)  *  2.5



Result:  INTERVAL  (38:45.0005)  MINUTE  TO  FRACTION(4)

In this example, 15 * 2.5 = 37.5 minutes, 30 * 2.5 = 75 seconds, and 2 * 2.5 = 5 fraction(4). The 0.5 minute is converted into 30 seconds and 60 seconds are converted into 1 minute, which produces the final result of 38 minutes, 45 seconds, and 0.0005 of a second. The results of any calculation include the same amount of precision as the original INTERVAL value.