DATETIME

The DATETIME data type stores an instant in time expressed as a calendar date and time of day. You choose how precisely a DATETIME value is stored; its precision can range from a year to a fraction of a second.

DATETIME stores a data value as a contiguous series of fields that represents each time unit (year, month, day, and so forth) in the data type declaration.

Field qualifiers to specify a DATETIME data type have this format:

DATETIME largest_qualifier TO smallest_qualifier

This resembles an INTERVAL field qualifier (see INTERVAL ), but DATETIME represents a point in time, rather than (like INTERVAL) a span of time. These differences exist between DATETIME and INTERVAL qualifiers:

• The DATETIME keyword replaces the INTERVAL keyword.
• DATETIME field qualifiers cannot specify a non-default precision for the largest_qualifier time unit.
• A DATETIME value that includes YEAR and/or MONTH time units can also include smaller time units, whereas an INTERVAL data type that stores days (or smaller time units) cannot store months or years.

The largest_qualifier and smallest_qualifier of a DATETIME data type can be any of the fields that Table 6 lists, provided that smallest_qualifier does not specify a larger time unit than largest_qualifier. (The largest and smallest time units can be the same; for example, DATETIME YEAR TO YEAR.)

The declaration of a DATETIME column need not include the full YEAR to FRACTION range of time units. It can include any contiguous subset of these time units, or even only a single time unit.

For example, you can enter a MONTH TO HOUR value in a column declared as YEAR TO MINUTE, as long as each entered value contains information for a contiguous series of time units. You cannot, however, enter a value for only the MONTH and HOUR; the entry must also include a value for DAY.

If you use the DB–Access TABLE menu, and you do not specify the DATETIME qualifiers, a default DATETIME qualifier, YEAR TO YEAR, is assigned.

A valid DATETIME literal must include the DATETIME keyword, the values to be entered, and the field qualifiers. You must include these qualifiers because, as noted earlier, the value that you enter can contain fewer fields than were declared for that column. Acceptable qualifiers for the first and last fields are identical to the list of valid DATETIME fields that Table 6 lists.

Write values for the field qualifiers as integers and separate them with delimiters. Table 7 lists the delimiters that are used with DATETIME values in the default U.S. English locale. (These are a superset of the delimiters that are used in INTERVAL values; see Table 9.)

Figure 2 shows a DATETIME YEAR TO FRACTION(3) value with delimiters.

Figure 2. Example DATETIME Value with Delimiters

When you enter a value with fewer time-unit fields than in the column, the value that you enter is expanded automatically to fill all the declared time-unit fields. If you leave out any more significant fields, that is, time units larger than any that you include, those fields are filled automatically with the current values for those time units from the system clock calendar. If you leave out any less-significant fields, those fields are filled with zeros (or with 1 for MONTH and DAY) in your entry.

You can also enter DATETIME values as character strings. The character string must include information for each field defined in the DATETIME column. The INSERT statement in the following example shows a DATETIME value entered as a character string:

INSERT INTO cust_calls (customer_num, call_dtime, user_id,
       call_code, call_descr)
     VALUES (101, '2001-01-14 08:45', 'maryj', 'D',
       'Order late - placed 6/1/00')

If call_dtime is declared as DATETIME YEAR TO MINUTE, the character string must include values for the year, month, day, hour, and minute fields.

If the character string does not contain information for all the declared fields (or if it adds additional fields), then the database server returns an error.

All fields of a DATETIME column are two-digit numbers except for the year and fraction fields. The year field is stored as four digits. When you enter a two-digit value in the year field, how the abbreviated year is expanded to four digits depends on the setting of the DBCENTURY environment variable.

For example, if you enter 02 as the year value, whether the year is interpreted as 1902, 2002, or 2102 depends on the setting of DBCENTURY and on the value of the system clock calendar at execution time. If you do not set DBCENTURY, then the leading digits of the current year are appended by default. For information about setting DBCENTURY, see DBCENTURY.

The fraction field requires n digits where 1 ≤ n ≤ 5, rounded up to an even number. You can use the following formula (rounded up to a whole number of bytes) to calculate the number of bytes that a DATETIME value requires:

(total number of digits for all fields) /2 + 1

For example, a YEAR TO DAY qualifier requires a total of eight digits (four for year, two for month, and two for day). According to the formula, this data value requires 5, or (8/2) + 1, bytes of storage.

For information on how to use DATETIME values in arithmetic and relational expressions, see Manipulating DATE with DATETIME and INTERVAL Values. For more information on the DATETIME data type, see the IBM Informix: Guide to SQL Syntax.

If you specify a locale other than U.S. English, the locale defines the culture-specific display formats for DATETIME values. To change the default display format, change the setting of the GL_DATETIME environment variable.

With an ESQL API, the DBTIME environment variable also affects DATETIME formatting. Non-default locales and settings of the GL_DATE and DBDATE environment variables also affect the display of datetime data. They do not, however, affect the internal storage format of a DATETIME column.

The USEOSTIME configuration parameter can affect the subsecond granularity when the database server obtains the current time from the operating system in SQL statements; for details, see the IBM Informix: Administrator's Reference.

For more information on DBTIME, see DBTIME. For more information on DBCENTURY, see DBCENTURY. For more information on locales and GLS environment variables that can specify end-user DATETIME formats, see the IBM Informix: GLS User's Guide.