Ask the Informix Pro 10-Minute Solution
 |
Date and Time Manipulation in Informix
By Joe Lumbley
Informix has extensive capabilities for working with time data. This type of data is very important in OLTP and data warehousing environments. Unfortunately, information about how to manipulate these values is spread across multiple Informix manuals; there is no one place that fully explains the intricacies of these items.
This 10-Minute Solution helps consolidate this information. Although it is not intended to be exhaustive, you should be able to get the general feel of what to use and where to use it. Go to the SQL Syntax and SQL Reference manuals for complete details on the use of these concepts.
Environment Variables
There are several variables that control aspects of the viewing and entering of DATE and DATETIME values that can be set by individual database users. Here are some of them.
DBDATE
The DBDATE environment variable specifies the format that is used for inserting and viewing DATE and DATETIME values. DBDATE contains the formats for:
- Order of MONTH, DAY, and YEAR in the date
- Number of digits for the YEAR, Y2 = 2 digits Y4 = 4 digits
- Separator between the MONTH, DAY, and YEAR. Default is "/" in US English
- For no separator, use 0 in the format. If you enter an invalid separator value, the separator defaults to the right slash ( / )
Possible values for the separator are:
- - Dashes
- . Period
- / Right slash
The YEAR is specified by the character Y. MONTH is M. DAY is D. The following formats are samples of possible values for DBDATE:
- MDY4/ 07/01/1950
- DMY2- 01-07-50
- MDY4 07/01/1950
- MDY20 070150
DBCENTURY
The YEAR portion of the DATETIME and DATE variables can be entered as either two or four digits. If the year is expressed as two digits rather than as four digits, the century is calculated by the value of the DBCENTURY environment variable. Possible values of DBCENTURY are:
- P = Past; assumes that the ambiguous date is in the past
- F = Future; assumes that any ambiguous date is in the future
- C = Closest; the past, present, and future values are evaluated and the date that is closest to the current date is used
- R = Present; an ambiguous date is assumed to be in the current century
If DBCENTURY is not set, the current century is used for ambiguous dates. Since DBCENTURY is an environment variable, different users can use different values and different behaviors, based upon their DBCENTURY setting.
Data Types
The three main data time-related types used in Informix are DATE, DATETIME, and INTERVAL.
DATE
The DATE data type stores a calendar date. It is a four-byte integer and is represented as the number of days since the beginning of the 20th century. Date 0 is December 31, 1899. Date 1 is January 1, 1900. In the US English locale, dates are in the format of MM/DD/YY: e.g., 12/31/99. This format can be changed by use of the DBDATE environment variable. It can also be changed by means of the TIME category in the locale file.
Because ATE values are integers, you can use aggregate functions on them, such as SUM, AVERAGE, MIN, and MAX.
All in all, the DATE datatype is fairly simple to understand. It's just a date, and that's fairly intuitive.
DATETIME
Compared to DATE, the DATETIME data type gives users the most problems. DATETIME represents a specific point in time for the era beginning at 1 A.D. It can store anything from just the year all the way to fractions of a second. To the computer, DATETIME is not a single format. Depending upon the requested precision, it can range in size from 2 to 11 bytes.
DATETIME data types are declared like this:
CREATE table test_datetime (mydatetime DATETIME YEAR to YEAR)
CREATE table test_datetime (mydatetime DATETIME YEAR to DAY)
CREATE table test_datetime (mydatetime DATETIME YEAR to FRACTION)
CREATE table test_datetime (mydatetime DATETIME HOUR to FRACTION(5))
This last part indicates the precision. It is <><>in the format of <lowest_granularity> to <highest_granularity>. The granularities determine the level of precision in the DATETIME. Possible granularities are:
- YEAR
- MONTH
- DAY
- HOUR
- MINUTE
- SECOND
- FRACTION
The lowest level of granularity is YEAR to YEAR. This would only show the year of the date. The highest level of granularity would be YEAR to FRACTION, which would show the entire date and time down to the fraction of a second. How fine the FRACTION granularity represents is up to your operating system. Some systems support fractional time to the millisecond, while some cannot handle fractions of a second at all.
The only stipulation is that the first granularity must be a larger time span than the second. Thus, a statement like this is nonsense and gives a syntax error:
CREATE table test_datetime (mydatetime DATETIME FRACTION(5) to YEAR)
The default size of the FRACTION is 3 decimal places. Up to 5 digits of precision are allowed and are specified by the "X" in the FRACTION(X) portion of the specification.
A DATETIME variable looks like this:
99-07-01 08:45:01.00010
Note that you have multiple delimiters for different elements of the format:
- Hyphen separates the YEAR from the MONTH and the MONTH from the DAY
- Space separates the DAY from the HOUR
- Colon separates HOUR from the MINUTE and the MINUTE from the SECOND
- Decimal point separates the SECOND from the FRACTION
Dealing with Partial DATETIME Values
If you enter less data into a DATETIME value than the format requires, the entered value is expanded to the appropriate number of data elements. If you leave out any of the more significant fields, the value of the CURRENT date is entered. For example, if "mydatetime" is declared as DATETIME YEAR to FRACTION and you enter only the time portion of the value, the YEAR to DAY data is replaced with the CURRENT date. If you leave out any less significant fields, the values are replaced with zeros for the time values or ones for missing MONTH or DAY values. Thus, if you entered just "1999" into mydatetime, the value would be "99-01-01 00:00:00.000."
Converting between DATE and DATETIME Values
You can convert between DATE and DATETIME values. If the DATETIME value is only YEAR to DAY, the conversions to and from DATE values simply convert the formats. Thus, a DATETIME YEAR to DAY value of "1950-07-01" is converted to "07/01/50" (or whatever format is defined in the DBDATE environment variable). Likewise, the reverse conversion would convert the "07/01/50" DATE to the "1950-07-01" DATETIME.
If you convert a DATE to a DATETIME YEAR to SECOND or to a DATETIME YEAR TO FRACTION, the missing fields will be replaced by zeros. Likewise, if you convert the other way, from DATETIME YEAR to SECOND or FRACTION to a DATE format, the extra fields are dropped.
INTERVAL
The third data type used in date/datetime manipulation is the INTERVAL. The INTERVAL data type represents the duration, or length of time, between two of either DATE or DATETIME values. There are two types of INTERVALS, one representing year-month value in the format of years and months, and one representing day-time intervals in the format of days, hours, minutes, seconds, and fractions.
INTERVAL values are declared like this:
INTERVAL first_value to second_value
If the first_value is earlier than the second_value, the INTERVAL is positive. If the first_value is later than the second_value, then the INTERVAL is negative.
The value qualifiers are the same as used in DATETIME data variables. Unlike DATETIME variables, you can specify INTERVALS with more digits than are in the DATETIME variables. For example, a DAY field in a DATETIME contains two digits. It is possible to extend this to cover more than two digits. For example, declaring myinterval INTERVAL DAY(4) to HOUR would allow up to 9,999 days.
When manipulating a DATETIME by an INTERVAL, both the DATETIME and the INTERVAL must have the same FIRST and LAST qualifiers. Thus it is not possible to subtract a DATETIME YEAR to FRACTION value from a DATETIME YEAR to DAY value without first making them both YEAR to FRACTION. You use the EXTEND function to make them similar.
|
|
|
