Chronological Data Types

Home | Previous Page | Next Page Basics of Database Design and Implementation > Choosing Data Types > Data Types >

Chronological Data Types

The chronological data types record time. The DATE data type stores a calendar date. DATETIME records a point in time to any degree of precision from a year to a fraction of a second. The INTERVAL data type stores a span of time, that is, a duration.

Calendar Dates: DATE

The DATE data type stores a calendar date. A DATE value is actually a signed integer whose contents are interpreted as a count of full days since midnight on December 31, 1899. Most often it holds a positive count of days into the current century.

The DATE format has ample precision to carry dates into the far future (58,000 centuries). Negative DATE values are interpreted as counts of days prior to the epoch date; that is, a DATE value of -1 represents December 30, 1899.

Because DATE values are integers, the values can be used in arithmetic expressions. For example, you can take the average of a DATE column, or you can add 7 or 365 to a DATE column. In addition, a rich set of functions exists specifically for manipulating DATE values. For more information, see the IBM Informix: Guide to SQL Syntax.

The DATE data type is compact, at 4 bytes per item. Arithmetic functions and comparisons execute quickly on a DATE column.

Choosing a Date Format (GLS)

You can punctuate and order the components of a date in many ways. When an application displays a DATE value, it refers to a date format that the user specifies. The default locale specifies a U.S. English date format of the form:

10/25/2001

To customize this date format, choose your locale appropriately or set the DBDATE environment variable. For more information, see the IBM Informix: Guide to SQL Reference.

For languages other than English, you can use the TIME category of the locale file to change the date format. For more information on how to use locales, refer to the IBM Informix: GLS User's Guide.

Exact Points in Time: DATETIME

The DATETIME data type stores any moment in time in the era that began 1 A.D. In fact, DATETIME is really a family of 28 data types, each with a different precision. When you define a DATETIME column, you specify its precision. The column can contain any sequence from the list: year, month, day, hour, minute, second, and fraction. Thus, you can define a DATETIME column that stores only a year, only a month and day, or a date and time that is exact to the hour or even to the millisecond. Table 1 shows that the size of a DATETIME value ranges from 2 to 11 bytes depending on its precision.

The advantage of DATETIME is that it can store specific date and time values. A DATETIME column typically requires more storage space than a DATE column, depending on the DATETIME qualifiers. Datetime also has an inflexible display format. For information about how to circumvent the display format, see Forcing the Format of a DATETIME or INTERVAL Value.

Table 1. Precisions for the DATETIME Data Type
Precision	Size*	Precision	Size*
year to year	3	day to hour	3
year to month	4	day to minute	4
year to day	5	day to second	5
year to hour	6	day to fraction(f)	5 + f/2
year to minute	7	hour to hour	2
year to second	8	hour to minute	3
year to fraction (f)	8 + f/2	hour to second	4
month to month	2	hour to fraction(f)	4 + f/2
month to day	3	minute to minute	2
month to hour	4	minute to second	3
month to minute	5	minute to fraction(f)	3 + f/2
month to second	6	second to second	2
month to fraction(f)	6 + f/2	second to fraction(f)	2 + f/2
day to day	2	fraction to fraction(f)	1 + f/2
* When f is odd, round the size to the next full byte

Durations using INTERVAL

The INTERVAL data type stores a duration, that is, a length of time. The difference between two DATETIME values is an INTERVAL, which represents the span of time that separates them. The following examples might help to clarify the differences:

An employee began working on January 21, 1997 (either a DATE or a DATETIME).
She has worked for 254 days (an INTERVAL value, the difference between the TODAY function and the starting DATE or DATETIME value).
She begins work each day at 0900 hours (a DATETIME value).
She works 8 hours (an INTERVAL value) with 45 minutes for lunch (another INTERVAL value).
Her quitting time is 1745 hours (the sum of the DATETIME when she begins work and the two INTERVALs).

Like DATETIME, INTERVAL is a family of data types with different precisions. An INTERVAL value can represent a count of years and months, or it can represent a count of days, hours, minutes, seconds, or fractions of seconds; 18 precisions are possible. The size of an INTERVAL value ranges from 2 to 12 bytes, depending on the formulas that Table 2 shows.

Table 2. Precisions for the INTERVAL Data Type
Precision	Size*	Precision	Size*
year(p) to year	1 + p/2	hour(p) to minute	2 + p/2
year(p) to month	2 + p/2	hour(p) to second	3 + p/2
month(p) to month	1 + p/2	hour(p) to fraction(f)	4 + (p + f)/2
day(p) to day	1 + p/2	minute(p) to minute	1 + p/2
day(p) to hour	2 + p/2	minute(p) to second	2 + p/2
day(p) to minute	3 + p/2	minute(p) to fraction(f)	3 + (p + f)/2
day(p) to second	4 + p/2	second(p) to second	1 + p/2
day(p) to fraction(f)	5 + (p + f)/2	second(p) to fraction(f)	2 + (p + f)/2
hour(p) to hour	1 + p/2	fraction to fraction(f)	1 + f/2
* Round a fractional size to the next full byte.

INTERVAL values can be negative as well as positive. You can add or subtract them, and you can scale them by multiplying or dividing by a number. This is not true of either DATE or DATETIME. You can reasonably ask, "What is one-half the number of days until April 23?" but not, "What is one-half of April 23?"

Forcing the Format of a DATETIME or INTERVAL Value

The database server always displays the components of an INTERVAL or DATETIME value in the order year-month-day hour:minute:second.fraction. It does not refer to the date format that is defined to the operating system, as it does when it formats a DATE value.

You can write a SELECT statement that displays the date part of a DATETIME value in the system-defined format. The trick is to isolate the component fields with the EXTEND function and pass them through the MDY() function, which converts them to a DATE. The following code shows a partial example:

SELECT ... MDY (
   EXTEND (DATE_RECEIVED, MONTH TO MONTH),
   EXTEND (DATE_RECEIVED, DAY TO DAY),
   EXTEND (DATE_RECEIVED, YEAR TO YEAR) )
   FROM RECEIPTS ...

Choosing a DATETIME Format (GLS)

When an application displays a DATETIME value, it refers to a DATETIME format that the user specifies. The default locale specifies a U.S. English DATETIME format of the following form:

2001-10-25 18:02:13

For languages other than English, use the TIME category of the locale file to change the DATETIME format. For more information on how to use locales, see the IBM Informix: GLS User's Guide.

To customize this DATETIME format, choose your locale appropriately or set the GL_DATETIME or DBTIME environment variable. For more information about these environment variables, see the IBM Informix: GLS User's Guide.