Informix DataBlade API Programmer's Manual Using Date and Time Data Types

The DataBlade API provides support for the following kinds of fixed-point data (which correspond to existing SQL data types).

Date and time data is an instant in time that is expressed as a calendar date and time of day. A date and/or time value can also have a precision (which is the number of digits required to store the value) and a scale (which is the end qualifier of the date/time).

Interval data is a span of time; that is, the number of units in either of the following interval classes:

• year-month intervals hold the number of years and months that have passed
• day-time intervals hold the number of days and hours that have passed

The DataBlade API provides support for date values in both their text and binary representations.

The text representation of a date, time, or interval value is a quoted string that contains a series of digits and symbols. The DataBlade API supports a text representation for both date/time and interval values as a quoted string with the formats that the following table shows.

The text representations in the preceding table use the following abbreviations:

A date or time value in its text representation is often called a date/time string. For example, the following date or time string contains the value for 2pm on July 12, 1999 with a qualifier of year to minute:

"1999-07-12 14:00:00"

Usually, a date or time string must match the qualifier of the date or time binary representation with which it is associated.

The following interval string indicates a passage of 3 years and 6 months:

"03-06"

A locale defines the end-user format of a date or time or interval value. The end-user format is the format in which data appears in a client application when the data is a literal string or character variable. The preceding strings are the end-user formats for the default locale, U.S. English. A nondefault locale can define date or time end-user formats that are particular to a country or culture outside the U.S. You can also customize the end-user format of a date with the GL_DATETIME environment variable. For more information, see the Informix Guide to GLS Functionality.

The DataBlade API supports the following SQL data types that can hold information about time values.

The SQL DATETIME data type holds the internal (binary) format of a date and/or time value. It encodes an instant in time expressed as a calendar date and time of day. You choose how precisely a DATETIME value is stored with a qualifier. The precision can range from a year to a fraction of a second. For a complete description of the SQL DATETIME data type, see the Informix Guide to SQL: Reference.

The DATETIME data type uses a machine-independent method to encode the date or time qualifiers. It stores the information in the dtime_t structure, as follows:

typedef  struct  dtime  {

	short  dt_qual;

	dec_t  dt_dec;

}  dtime_t;

The dtime structure and dtime_t typedef have two parts, which Figure 4-1 shows.

Tip: The internal format of the DATETIME data type is often referred to as its binary representation.

The DataBlade API supports the SQL DATETIME data type with the mi_datetime data type. Therefore, the mi_datetime data type holds the binary representation of a date and/or time value.

The mi_datetime data type cannot fit into an MI_DATUM. Therefore, it must be passed by reference within C user-defined routines.

All data types, including mi_datetime, must be passed by reference within client LIBMI applications.

Because the binary representation of a DATETIME (mi_datetime) value is an Informix-proprietary format, you cannot use standard system functions to perform operations on mi_datetime values. Instead, the DataBlade API provides the following support for the DATETIME data type.

The SQL INTERVAL data type holds the internal (binary) format of an interval value. It encodes a value that represents a span of time. INTERVAL types are divided into two classes: year-month intervals and day-time intervals. A year-month interval can represent a span of years and months, and a day-time interval can represent a span of days, hours, minutes, seconds, and fractions of a second. For a complete description of the SQL INTERVAL data type, see the Informix Guide to SQL: Reference.

The INTERVAL data type uses a machine-independent method to encode the interval qualifiers. It stores the information in the intrvl_t structure, as follows:

typedef  struct  intrvl  {

	short  in_qual;

	dec_t  in_dec;

}  intrvl_t;

The intrvl structure and intrvl_t typedef have the two parts that Figure 4-2 shows.

Tip: The internal format of the INTERVAL data type is often referred to as its binary representation.

The DataBlade API supports the SQL INTERVAL data type with the mi_interval data type. Therefore, an mi_interval data type holds the binary representation of an interval value.

The mi_interval data type cannot fit into an MI_DATUM. Therefore, it must be passed by reference within C user-defined routines.

All data types, including mi_interval, must be passed by reference within client LIBMI applications.

Because the binary representation of an INTERVAL (mi_interval) value is an Informix-proprietary format, you cannot use standard system functions to perform operations on mi_interval values. Instead, the DataBlade API provides the following support for the INTERVAL data type.

The datetime.h header file contains definitions for use with the DATETIME and INTERVAL data types. The header file datetime.h contains the declarations for the date, time, and interval data types, as follows:

• The internal format represents DATETIME and mi_datetime values with the dtime_t structure.
• The internal format represents INTERVAL and mi_interval values with the intrvl_t structure.

In addition to these data structures, the datetime.h file defines the constants and macros for date and time qualifiers that Figure 4-3 shows.

Tip: For a complete list of date and time macros, consult the datetime.h header file that is installed with your database server. This header file resides in the incl/public subdirectory of the INFORMIXDIR directory.

Figure 4-3 on page 4-15 shows the macro definitions that you can use to compose qualifier values. You need these macros only when you work directly with qualifiers in binary form. For example, if your program does not provide an mi_interval qualifier in the variable declaration, you need to use the mi_interval qualifier macros to initialize and set the mi_interval variable, as the following example shows:

/* Declare a variable without a qualifier */

	mi_interval intvl1;


/* Set the interval qualifier for the variable */

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


/* Assign a value to the variable */

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

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

In addition to the declaration of the dtime_t typedef and the preceding date and time macros, the datetime.h header file declares the DATETIME-type functions of the ESQL/C library. The mitypes.h header file automatically includes datetime.h. In turn, the milib.h header file automatically includes mitypes.h and mi.h automatically includes milib.h. Therefore, you automatically have access to the dtime_t and intrvl_t structures, the mi_datetime and mi_interval data types, any of the date or time macros, or any of the ESQL/C DATETIME-type functions when you include mi.h in your DataBlade API module.

When an application fetches or inserts a DATETIME or INTERVAL value, the DataBlade API module must ensure that the qualifier field of the variable is valid:

• When an application fetches a DATETIME value into an mi_datetime variable or inserts a DATETIME value from an mi_datetime 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 mi_interval variable or inserts an INTERVAL value from an mi_interval variable, it must ensure that the in_qual field of the intrvl_t structure is valid.

When a DataBlade API module uses an mi_datetime variable to fetch or insert a DATETIME value, the module must find a valid qualifier in the mi_datetime variable. The DataBlade API takes one the following actions; based on the value of the dt_qual field in the dtime_t structure that is associated with the variable:

• When the dt_qual field contains a valid qualifier, the DataBlade API 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 dtextend() function.

• When the dt_qual field does not contain a valid qualifier, the DataBlade API takes different actions for a fetch and an insert:
• For a fetch, the DataBlade API uses the DATETIME column value and its qualifier to initialize the mi_datetime variable.

Zero is an invalid qualifier. Therefore, if you set the dt_qual field to zero, you can ensure that the DataBlade API uses the qualifier of the DATETIME column.

• For an insert, the DataBlade API cannot perform the insert or update operation.

The DataBlade API 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 a DataBlade API module uses an mi_interval variable to fetch or insert an INTERVAL value, the DataBlade API must find a valid qualifier in the mi_interval variable. The DataBlade API takes one of the following actions; based on the value of the in_qual field the intrvl_t structure that is associated with the variable:

• When the in_qual field contains a valid qualifier, the DataBlade API 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, the DataBlade API 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, the DataBlade API 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 invextend() function.

• When the in_qual field does not contain a valid qualifier, the DataBlade API 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, the DataBlade API uses the INTERVAL column value and its qualifier to initialize the mi_interval 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, the DataBlade API cannot perform the insert or update operation.

The DataBlade API 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 select a DATETIME or INTERVAL column value into a character variable. The DataBlade API converts the DATETIME or INTERVAL column value to a character string before it stores it in the character variable. This character string conforms to the 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 (mi_double_precision, mi_integer, and so on) variables.

You can also insert a DATETIME or INTERVAL column value from a character variable. The DataBlade API 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, the DataBlade API 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 and mi_date variables to DATETIME and INTERVAL column values.

Transferring Date and Time Data

For date/time and interval data to be portable when transferred across different computer architectures, the DataBlade API provides the following functions to handle type alignment and byte order.

The mi_get_datetime, mi_get_interval(), mi_put_datetime(), and mi_put_interval() functions are useful in the send and receive support function of an opaque data type that contains mi_datetime or mi_interval values. They allow you to ensure that DATETIME or INTERVAL values remained aligned when transferred to and from client applications. For more information, see Converting Opaque-Type Data With Machine-Specific Data Types.

Both the DataBlade API library and the ESQL/C library provide functions that convert from the text (string) representation of a date, time, or interval value to the binary (internal) representation for DATETIME or INTERVAL, respectively.

The DataBlade API provides the following functions for conversion between text and binary representations of date, time, and interval data.

The mi_datetime_to_string(), mi_interval_to_string(), mi_string_to_datetime(), and mi_string_to_interval() functions convert DATETIME and INTERVAL values to and from the ANSI SQL standards formats for these data types.

The mi_datetime_to_string(), mi_interval_to_string(), mi_string_to_datetime(), and mi_string_to_interval() functions are useful in the input and output support function of an opaque data type that contains mi_datetime and mi_interval values, as long as these values use the ANSI SQL formats. They allow you to convert DATETIME and INTERVAL values between their external format (text) and their internal (binary) format when transferred to and from client applications. For more information, see Converting Opaque-Type Data Between Text and Binary Representations.

The ESQL/C function library provides functions for conversion between text and binary representations of date, time, and interval data.

The ESQL/C library provides the following functions that convert internal DATETIME (mi_datetime) values to and from char strings.

The dttoasc() and dtcvasc() functions convert mi_datetime values to and from the ANSI SQL standards for DATETIME strings. 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 dttofmtasc() and dtcvfmtasc() functions convert mi_datetime values to and from a date or time string using a time-formatting mask. This time-formatting mask contains the same formatting directives that the DBTIME environment variable supports. (For a list of these directives, refer to the description of DBTIME in the Informix Guide to SQL: Reference.)

The dtextend() function extends an mi_datetime value to a different qualifier. You can use it to convert between DATETIME and DATE values.

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

The ESQL/C library provides the following functions that convert internal INTERVAL (mi_interval) values to and from char text.

The intoasc() and incvasc() functions convert mi_interval values to and from the ANSI SQL standards for INVERVAL strings. The ANSI SQL standards specify qualifiers and formats for character representations of DATETIME and INTERVAL values. 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.

The intofmtasc() and incvfmtasc() functions convert mi_interval values to and from an interval string using a time-formatting mask. This time-formatting mask contains the same formatting directives that the DBTIME environment variable supports. (For a list of these directives, refer to the description of DBTIME in the Informix Guide to SQL: Reference.)

The ESQL/C library provides the following functions to perform operations on DATETIME (mi_datetime) and INTERVAL (mi_interval) values.

Any other operations, modifications, or analyses can produce unpredictable results.

Figure 4-4 shows the DataBlade API functions that obtain qualifier information of a DATETIME (mi_datetime) or INTERVAL (mi_interval) value.

Suppose you have a table with a single column, dt_col, of type DATETIME YEAR TO SECOND. If row_desc is a row descriptor for a row with this table, the code fragment in Figure 4-5 obtains the name, qualifier, precision, and scale for this column value.

Figure 4-5 Obtaining Type Information for a DATETIME Value

In Figure 4-5, the value in the type_buf buffer for a row of the is:

column=0, type name=datetime year to second, qualifier=3594 precision=14 scale=10

The mi_type_qualifier() function returns the encoded qualifier of a DATETIME or INTERVAL data type from a type descriptor. This qualifier is the internal value that the database server uses to track the complete qualifier range, from the starting field to the end field. It is the value stored in the collength column of the syscolumns table for DATETIME and INTERVAL columns. You can use the qualifier macros and constants (see Figure 4-3 on page 4-15) to interpret this encoded value.

In Figure 4-5, the value in type_qual contains the encoded integer qualifier (3594) for the dt_col column. You can obtain the starting qualifier for the DATETIME from the encoded qualifier with the TU_START macro, as follows:

TU_START(type_qual)

This TU_START call yields 0, which is the value of the TU_YEAR constant in datetime.h header file. You can obtain also the ending qualifier for the DATETIME from the encoded qualifier with the TU_END macro, as follows:

TU_END(type_qual)

This TU_END call yields 10, which is the value of the TU_SECOND constant in the datetime.h header file. Therefore, the encoded qualifier 3594 represents the qualifier year to second.

For the DATETIME and INTERVAL data types, the precision is the number of digits required to store a value with the specified qualifier. In Figure 4-5, the call to the mi_type_precision() function saves in type_prec the precision for the dt_col column from its type descriptor. This precision has a value of 14 because a DATETIME YEAR TO SECOND value requires 14 digits:

YYYYMMDDHHMMSS

 



YYYY

is the 4-digit year.


MM

is the 2-digit month.


DD

is the 2-digit day of the month.


HH

is the 2-digit hour.


MM

is the 2-digit minute.


SS

is the 2-digit second.

The DataBlade API also provides functions that obtain DATETIME or INTERVAL precision of a column associated with an input parameter, a UDR argument, UDR return value, or a row column. For a list of these functions, see Figure 4-4 on page 4-25.

For the DATETIME and INTERVAL data types, the scale is the encoded integer value for the end qualifier. In Figure 4-5, the call to the mi_type_scale() function stores in type_scale the scale for the dt_col column. This precision has a value of 10 because the end qualifier for the DATETIME YEAR TO SECOND data type is SECOND, whose encoded value (TU_SECOND) is 10.

The DataBlade API also provides functions that obtain DATETIME or INTERVAL scale of an input parameter, a UDR argument, UDR return value, or column. For a list of these functions, see Figure 4-4 on page 4-25.