INFORMIX
Informix-ESQL/C Programmer's Manual
Chapter 6: Working with Time Data Types
Home Contents Index Master Index New Book

The SQL DATETIME and INTERVAL Data Types

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

Figure 6-1 summarizes these two time data types.

Figure 6-1
ESQL/C Time Data Types
SQL Data Type ESQL/C Data Type C typedef Name Sample Declaration

DATETIME

datetime

dtime_t

EXEC SQL BEGIN DECLARE SECTION;

datetime year to day sale;

EXEC SQL END DECLARE SECTION;

INTERVAL

interval

intrvl_t

EXEC SQL BEGIN DECLARE SECTION;

interval hour to second test_num;

EXEC SQL END DECLARE SECTION;

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:

The decimal.h header file defines the type dec_t, which is a component of these 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:

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:

For more information on the DATETIME and INTERVAL data types, refer to the Informix Guide to SQL: Reference.

The datetime Data Type

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:

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

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

Figure 6-2
Fields in the dtime Structure
Field Description

dt_qual

The qualifier of the datetime value.

dt_dec

The digits of the fields of the datetime value. This field is a decimal value.

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

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 on page 6-31.

The interval Data Type

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:

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

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

Figure 6-3
Fields in the intrvl Structure
Field Description

in_qual

The qualifier of the interval value.

in_dec

The digits of the fields of the interval value. This field is a decimal value.

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:

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 shown on page 6-31.

Macros for datetime and interval Data Types

In addition to these data structures, the datetime.h file defines the names and macro functions that Figure 6-4 shows.

Figure 6-4
Qualifier Macros for datetime and interval Data Types

(1 of 2)

Name of Macro Description

TU_YEAR

The time unit for the YEAR qualifier field

TU_MONTH

The time unit for the MONTH qualifier field

TU_DAY

The time unit for the DAY qualifier field

TU_HOUR

The time unit for the HOUR qualifier field

TU_MINUTE

The time unit for the MINUTE qualifier field

TU_SECOND

The time unit for the SECOND qualifier field

TU_FRAC

The time unit for the leading qualifier field of FRACTION

TU_Fn

The names for datetime ending fields of FRACTION(n), for n from 1 to 5

TU_START(q)

Returns the leading field number from qualifier q

TU_END(q)

Returns the trailing field number from qualifier q

TU_LEN(q)

Returns the length in digits of the qualifier q

TU_FLEN(f)

Returns the length in digits of the first field, f, of an interval qualifier

TU_ENCODE(p,f,t)

Creates a qualifier from the first field number f with precision p and trailing field number t

TU_DTENCODE(f,t)

Creates a datetime qualifier from the first field number f and trailing field number t

TU_IENCODE(p,f,t)

Creates an interval qualifier from the first field number f with precision p and trailing field number t

You need the macros in Figure 6-4 only when you work 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. This is shown in the following example:

In the previous example, the interval variable gets a day to second qualifier. The precision of the largest field in the qualifier, day, is set to 2.

Fetching and Inserting DATETIME and INTERVAL Values

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:

Fetching and Inserting into datetime Host Variables

 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:

    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.

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

      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.

Fetching and Inserting into interval Host Variables

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:

    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.

      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.

Implicit Data Conversion

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. For more information, see "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.

Important: Informix products do not support automatic data conversion from DATETIME and INTERVAL column values to numeric (double, int, and so on) host variables.
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. For more information, see "ANSI SQL Standards for DATETIME and INTERVAL Values".

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.

Important: Informix products do not support automatic data conversion from numeric (double, int, and so on) and 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:

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

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

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

Operations on datetime and interval Values

The ESQL/C datetime and interval data types uses their own internal structure to store DATETIME and INTERVAL values. (For more information, see "The datetime Data Type" and "The interval Data Type".) Use the following ESQL/C library functions to perform all operations on datetime and interval host variables.

dtaddinv()

dtsub()

invdivdbl()

invmuldbl()

dtcurrent()

dtsubinv()

invdivinv()

For more information on how to use these functions, refer to "DATETIME and INTERVAL Library Functions". For more information on operations on DATETIME and INTERVAL values, see the Informix Guide to SQL: Reference.

Data Conversion for datetime and interval Values

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.

To convert a DATETIME value to a DATE value

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.

To convert a DATE value into a DATETIME 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.



Informix-ESQL/C Programmer's Manual, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.