INFORMIX-ESQL/C Programmer's Manual Working with Time Data Types

ESQL/C supports two data types that can hold information about time values:

• The datetime data type, which encodes an instant in time as a calendar date and a time of day.
• The interval data type, which encodes a span of time.

Figure 6-1 summarizes these two time data types.

The header file datetime.h contains the dtime_t and intrvl_t structures, along with a number of macro definitions that you can use to compose qualifier values. Include this file in all C source files that use any datetime or interval host variables:

EXEC SQL include datetime;

The decimal.h header file defines the type dec_t, which is a component of the dtime_t and intrvl_t structures.

Because of the multiword nature of these data types, it is not possible to declare an uninitialized datetime or interval host variable named year, month, day, hour, minute, second, or fraction. Avoid the following declarations:

EXEC SQL BEGIN DECLARE SECTION;

	datetime  year;  						/*  will  cause  an  error  */

	datetime  year  to  day  year,  today;  						/*  ambiguous  */

EXEC SQL END DECLARE SECTION;

A datetime or interval data type is stored as a decimal number with a scale factor of zero and a precision equal to the number of digits that its qualifier implies. Once you know the precision and scale, you know the storage format. For example, if you define a table column as DATETIME YEAR TO DAY, it contains four digits for year, two digits for month, and two digits for day, for a total of eight digits. It is thus stored as if it were decimal(8,0).

If the default precision of the underlying decimal value is not appropriate, you can specify a different precision. For example, if you have a host variable of type interval, with the qualifier day to day, the default precision of the underlying decimal value is two digits. If you have intervals of one hundred or more days, this precision is not adequate. You can specify a precision of three digits as follows:

interval day(3) to day;

For more information on the DATETIME and INTERVAL data types, ../sqlr/intro.htmler to the Informix Guide to SQL: Reference.

Use the datetime data type to declare host variables for database values of type DATETIME. You specify the accuracy of the datetime data type with a qualifier. For example, the qualifier in the following declaration is year to day:

datetime year to day sale;

As a host variable, a dtime_t. structure represents a datetime value:

typedef  struct  dtime  {

	short  dt_qual;

	dec_t  dt_dec;

}  dtime_t;

The dtime structure and dtime_t typedef have two parts. Figure 6-2 lists these parts.

Declare a host variable for a DATETIME column with the datetime data type followed by an optional qualifier, as the following example shows:

EXEC SQL include datetime;


EXEC SQL BEGIN DECLARE SECTION;

	datetime  year  to  day  holidays[10];

	datetime  hour  to  second  wins,  places,  shows;

	datetime  column6;

EXEC SQL END DECLARE SECTION;

If you omit the qualifier from the declaration of the datetime host variable, as in the last example, your program must explicitly initialize the qualifier with the macros shown in Figure 6-4 on page 6-10.

Use the interval data type to declare host variables for database values of type INTERVAL. You specify the accuracy of the interval data type with a qualifier. The qualifier in the following declaration is hour to second:

interval hour to second test_run;

As a host variable, an intrvl_t. represents an interval value:

typedef  struct  intrvl  {

	short  in_qual;

	dec_t  in_dec;

}  intrvl_t;

The intrvl structure and intrvl_t typedef have two parts. Figure 6-3 lists these parts.

To declare a host variable for an INTERVAL column, use the interval data type followed by an optional qualifier, as shown in the following example:

EXEC SQL BEGIN DECLARE SECTION;

	interval  day(3)  to  day  accrued_leave,  leave_taken;

	interval  hour  to  second  race_length;

	interval  scheduled;

EXEC SQL END DECLARE SECTION;

If you omit the qualifier from the declaration of the interval host variable, as in the last example, your program must explicitly initialize the qualifier with the macros described in the following section.

In addition to the datetime and interval data structures, the datetime.h file defines the macro functions shown in Figure 6-4 for working directly with qualifiers in binary form.

For example, if your program does not provide an interval qualifier in the host-variable declaration, you need to use the interval qualifier macros to initialize and set the interval host variable. In the following example, the interval variable gets a day to second qualifier. The precision of the largest field in the qualifier, day, is set to 2:

/* declare a host variable without a qualifier */

EXEC SQL BEGIN DECLARE SECTION;

	interval inv1;

EXEC SQL END DECLARE SECTION;


/* set the interval qualifier for the host variable */

inv1.in_qual = TU_IENCODE(2, TU_DAY, TU_SECOND);


/* assign values to the host variable */

incvasc ("5 2:10:02", &inv1);

When an application fetches or inserts a DATETIME or INTERVAL value, ESQL/C must ensure that the qualifier field of the host variable is valid:

• When an application fetches a DATETIME value into a datetime host variable or inserts a DATETIME value from a datetime host variable, it must ensure that the dt_qual field of the dtime_t structure is valid.
• When an application fetches an INTERVAL value into an interval host variable or inserts an INTERVAL value from an interval host variable, it must ensure that the in_qual field of the intrvl_t structure is valid.

When an application uses a datetime host variable to fetch or insert a DATETIME value, ESQL/C must find a valid qualifier in the datetime host variable. ESQL/C takes one of the following actions, based on the value of the dt_qual field in the dtime_t structure that is associated with the host variable:

• When the dt_qual field contains a valid qualifier, ESQL/C extends the column value to match the dt_qual qualifier.

Extending is the operation of adding or dropping fields of a DATETIME value to make it match a given qualifier. You can explicitly extend DATETIME values with the SQL EXTEND function and the ESQL/C dtextend() function.

• When the dt_qual field does not contain a valid qualifier, ESQL/C takes different actions for a fetch and an insert:
• For a fetch, ESQL/C uses the DATETIME column value and its qualifier to initialize the datetime host variable.

Zero (0) is an invalid qualifier. Therefore, if you set the dt_qual field to zero, you can ensure that ESQL/C uses the qualifier of the DATETIME column.

• For an insert, ESQL/C cannot perform the insert or update operation.

ESQL/C sets the SQLSTATE status variable to an error-class code (and SQLCODE to a negative value) and the update or insert operation on the DATETIME column fails.

When an application uses an interval host variable to fetch or insert an INTERVAL value, ESQL/C must find a valid qualifier in the interval host variable. ESQL/C takes one of the following actions, based on the value of the in_qual field of the intrvl_t structure that is associated with the host variable:

• When the in_qual field contains a valid qualifier, ESQL/C checks it for compatibility with the qualifier from the INTERVAL column value.

The two qualifiers are compatible if they belong to the same interval class: either year to month or day to fraction. If the qualifiers are incompatible, ESQL/C sets the SQLSTATE status variable to an error-class code (and SQLCODE is set to a negative value) and the select, update, or insert operation fails.

If the qualifiers are compatible but not the same, ESQL/C extends the column value to match the in_qual qualifier. Extending is the operation of adding or dropping fields within one of the interval classes of an INTERVAL value to make it match a given qualifier. You can explicitly extend INTERVAL values with the ESQL/C invextend() function.

• When the in_qual field does not contain a valid qualifier, ESQL/C takes different actions for a fetch and an insert:
• For a fetch, if the in_qual field contains zero or is not a valid qualifier, ESQL/C uses the INTERVAL column value and its qualifier to initialize the interval host variable.
• For an insert, if the in_qual field is not compatible with the INTERVAL column or if it does not contain a valid value, ESQL/C cannot perform the insert or update operation.

ESQL/C sets the SQLSTATE status variable to an error-class code (and SQLCODE is set to a negative value) and the update or insert operation on the INTERVAL column fails.

You can fetch a DATETIME or INTERVAL column value into a character (char, string, or fixchar) host variable. ESQL/C converts the DATETIME or INTERVAL column value to a character string before it stores it in the character host variable. This character string conforms to the ANSI SQL standards for DATETIME and INTERVAL values. If the host variable is too short, ESQL/C sets sqlca.sqlwarn.sqlwarn1 to W, fills the host variable with asterisk ( * ) characters, and sets any indicator variable to the length of the untruncated character string.

You can also insert a DATETIME or INTERVAL column value from a character (char, string, fixchar, or varchar) host variable. ESQL/C uses the data type and qualifiers of the column value to convert the character value to a DATETIME or INTERVAL value. It expects the character string to contain a DATETIME or INTERVAL value that conforms to ANSI SQL standards.

If the conversion fails, ESQL/C sets the SQLSTATE status variable to an error-class code (and SQLCODE status variable to a negative value) and the update or insert operation fails.

For more information, see ANSI SQL Standards for DATETIME and INTERVAL Values.

Important: Informix products do not support automatic data conversion from DATETIME and INTERVAL column values to numeric (double, int, and so on) host variables. Nor do Informix products support automatic data conversion from numeric (double, int, and so on) or date host variables to DATETIME and INTERVAL column values.

ANSI SQL Standards for DATETIME and INTERVAL Values

The ANSI SQL standards specify qualifiers and formats for character representations of DATETIME and INTERVAL values. The standard qualifier for a DATETIME value is YEAR TO SECOND, and the standard format is as follows:

YYYY-MM-DD HH:MM:SS

The standards for an INTERVAL value specify the following two classes of intervals:

• The YEAR TO MONTH class has the following format:
YYYY-MM

A subset of this format is also valid: for example, just a month interval.

• The DAY TO FRACTION class has the following format:
DD HH:MM:SS.F

Any subset of contiguous fields is also valid: for example, MINUTE TO FRACTION.

You can use the ESQL/C library functions dtcvasc(), dtcvfmtasc(), dttoasc(), and dttofmtasc() to explicitly convert between DATETIME column values and character strings. To explicitly convert between INTERVAL column values and character strings, you can use the ESQL/C library functions incvasc(), incvfmtasc(), intoasc(), and intofmtasc().

For example, you can perform conversions between the DATETIME and DATE data types with ESQL/C library functions and intermediate strings.

1. Use dtextend() to adjust the DATETIME qualifier to year to day.
2. Apply dttoasc() to create a character string in the form yyyy-mm-dd.
3. Use rdefmtdate() with a pattern argument of yyyy-mm-dd to convert the string to a DATE value.

1. Declare a host variable with a qualifier of year to day (or initialize the qualifier with the value that the TU_DTENCODE(TU_YEAR,TU_DAY) macro returns).
2. Use rfmtdate() with a pattern of yyyy-mm-dd to convert the DATE value to a character string.
3. Use dtcvasc()to convert the character string to a value in the prepared DATETIME variable.
4. If necessary, use dtextend() to adjust the DATETIME qualifier.