DATE and DATETIME data values represent zero-dimensional points in time; INTERVAL data values represent 1-dimensional spans of time, with positive or negative values. DATE precision is always an integer count of days, but various field qualifiers can define the DATETIME and INTERVAL precision. You can use DATE, DATETIME, and INTERVAL data in arithmetic and relational expressions. You can manipulate a DATETIME value with another DATETIME value, an INTERVAL value, the current time (specified by the keyword CURRENT), or some unit of time (using the keyword UNITS).
You can use a DATE value in most contexts where a DATETIME value is valid, and vice versa. You also can use an INTERVAL operand in arithmetic operations where a DATETIME value is valid. In addition, you can add two INTERVAL values and multiply or divide an INTERVAL value by a number.
An INTERVAL column can hold a value that represents the difference between two DATETIME values or the difference between (or sum of) two INTERVAL values. In either case, the result is a span of time, which is an INTERVAL value. Conversely, if you add or subtract an INTERVAL from a DATETIME value, another DATETIME value is produced, because the result is a specific time.
Table 10 lists the binary arithmetic operations that you can perform on DATE, DATETIME, and INTERVAL operands, as well as the data type that is returned by the arithmetic expression.
No other combinations are allowed. You cannot add two DATETIME values because this operation does not produce either a specific time or a span of time. For example, you cannot add December 25 and January 1, but you can subtract one from the other to find the time span between them.
You can subtract most DATETIME values from each other. Dates can be in any order and the result is either a positive or a negative INTERVAL value. The first DATETIME value determines the precision of the result, which includes the same time units as the first operand.
If the second DATETIME value has fewer fields than the first, the precision of the second operand is increased automatically to match the first.
In the following example, subtracting the DATETIME YEAR TO HOUR value from the DATETIME YEAR TO MINUTE value results in a positive interval value of 60 days, 1 hour, and 30 minutes. Because minutes were not included in the second operand, the database server sets the minutes value for the second operand to 0 before performing the subtraction.
DATETIME (2003-9-30 12:30) YEAR TO MINUTE - DATETIME (2003-8-1 11) YEAR TO HOUR Result: INTERVAL (60 01:30) DAY TO MINUTE
If the second DATETIME operand has more fields than the first (regardless of whether the precision of the extra fields is larger or smaller than those in the first operand), the additional time unit fields in the second value are ignored in the calculation.
In the next expression (and its result), the year is not included for the second operand. Therefore, the year is set automatically to the current year (from the system clock-calendar), in this example 2005, and the resulting INTERVAL is negative, which indicates that the second date is later than the first.
DATETIME (2005-9-30) YEAR TO DAY
- DATETIME (10-1) MONTH TO DAY
Result: INTERVAL (-1) DAY TO DAY [assuming that the current
year is 2005]
INTERVAL values can be added to or subtracted from DATETIME values. In either case, the result is a DATETIME value. If you are adding an INTERVAL value to a DATETIME value, the order of values is unimportant; however, if you are subtracting, the DATETIME value must come first. Adding or subtracting a positive INTERVAL value simply moves the DATETIME result forward or backward in time. The expression shown in the following example moves the date ahead by three years and five months:
DATETIME (2000-8-1) YEAR TO DAY + INTERVAL (3-5) YEAR TO MONTH Result: DATETIME (2004-01-01) YEAR TO DAY
In most situations, the database server automatically adjusts the calculation when the operands do not have the same precision. In certain contexts, however, you must explicitly adjust the precision of one value to perform the calculation. If the INTERVAL value you are adding or subtracting has fields that are not included in the DATETIME value, you must use the EXTEND function to increase the precision of the DATETIME value. (For more information on the EXTEND function, see the Expression segment in the IBM Informix: Guide to SQL Syntax.)
For example, you cannot subtract an INTERVAL MINUTE TO MINUTE value from the DATETIME value in the previous example that has a YEAR TO DAY field qualifier. You can, however, use the EXTEND function to perform this calculation, as the following example shows:
EXTEND (DATETIME (2003-8-1) YEAR TO DAY, YEAR TO MINUTE) - INTERVAL (720) MINUTE(3) TO MINUTE Result: DATETIME (2003-07-31 12:00) YEAR TO MINUTE
The EXTEND function allows you to explicitly increase the DATETIME precision from YEAR TO DAY to YEAR TO MINUTE. This allows the database server to perform the calculation, with the resulting extended precision of YEAR TO MINUTE.
You can use DATE operands in some arithmetic expressions with DATETIME or INTERVAL operands by writing expressions to do the manipulating, as Table 11 shows.
| Expression | Result |
|---|---|
| DATE – DATETIME | INTERVAL |
| DATETIME – DATE | INTERVAL |
| DATE + or – INTERVAL | DATETIME |
In the cases that Table 11 shows, DATE values are first converted to their corresponding DATETIME equivalents, and then the expression is evaluated by the rules of arithmetic.
Although you can interchange DATE and DATETIME values in many situations, you must indicate whether a value is a DATE or a DATETIME data type. A DATE value can come from the following sources:
A DATETIME value can come from the following sources:
The database locale defines the default DATE and DATETIME formats. For the default locale, U.S. English, these formats are 'mm/dd/yy' for DATE values and 'yyyy-mm-dd hh:MM:ss' for DATETIME values.
To represent DATE and DATETIME values as character strings, the fields in the strings must be in proper order. In other words, when a DATE value is expected, the string must be in DATE format and when a DATETIME value is expected, the string must be in DATETIME format. For example, you can use the string 10/30/2003 as a DATE string but not as a DATETIME string. Instead, you must use 2003-10-30 or 03-10-30 as the DATETIME string.
In a nondefault locale, literal DATE and DATETIME strings must match the formats that the locale defines. For more information, see the IBM Informix: GLS User's Guide.
You can customize the DATE format that the database server expects with the DBDATE and GL_DATE environment variables. You can customize the DATETIME format that the database server expects with the DBTIME and GL_DATETIME environment variables. For more information, see DBDATE and DBTIME. For more information on all these environment variables, see the IBM Informix: GLS User's Guide.
You can also subtract one DATE value from another DATE value, but the result is a positive or negative INTEGER count of days, rather than an INTERVAL value. If an INTERVAL value is required, you can either use the UNITS DAY operator to convert the INTEGER value into an INTERVAL DAY TO DAY value, or else use EXTEND to convert one of the DATE values into a DATETIME value before subtracting.
For example, the following expression uses the DATE( ) function to convert character string constants to DATE values, calculates their difference, and then uses the UNITS DAY keywords to convert the INTEGER result into an INTERVAL value:
(DATE ('5/2/1994') - DATE ('4/6/1955')) UNITS DAY
Result: INTERVAL (12810) DAY(5) TO DAY
If you need YEAR TO MONTH precision, you can use the EXTEND function on the first DATE operand, as the following example shows:
EXTEND (DATE ('5/2/1994'), YEAR TO MONTH) - DATE ('4/6/1955')
Result: INTERVAL (39-01) YEAR TO MONTH
The resulting INTERVAL precision is YEAR TO MONTH, because the DATETIME value came first. If the DATE value had come first, the resulting INTERVAL precision would have been DAY(5) TO DAY.
You can add or subtract INTERVAL values only if both values are from the same class; that is, if both are year-month or both are day-time. In the following example, a SECOND TO FRACTION value is subtracted from a MINUTE TO FRACTION value:
INTERVAL (100:30.0005) MINUTE(3) TO FRACTION(4) - INTERVAL (120.01) SECOND(3) TO FRACTION Result: INTERVAL (98:29.9905) MINUTE TO FRACTION(4)
The use of numeric qualifiers alerts the database server that the MINUTE and FRACTION in the first value and the SECOND in the second value exceed the default number of digits.
When you add or subtract INTERVAL values, the second value cannot have a field with greater precision than the first. The second INTERVAL, however, can have a field of smaller precision than the first. For example, the second INTERVAL can be HOUR TO SECOND when the first is DAY TO HOUR. The additional fields (in this case MINUTE and SECOND) in the second INTERVAL value are ignored in the calculation.
You can multiply or divide INTERVAL values by numbers. Any remainder from the calculation is ignored, however, and the result is truncated to the precision of the INTERVAL. The following expression multiplies an INTERVAL value by a literal number that has a fractional part:
INTERVAL (15:30.0002) MINUTE TO FRACTION(4) * 2.5 Result: INTERVAL (38:45.0005) MINUTE TO FRACTION(4)
In this example, 15 * 2.5 = 37.5 minutes, 30 * 2.5 = 75 seconds, and 2 * 2.5 = 5 FRACTION (4). The 0.5 minute is converted into 30 seconds and 60 seconds are converted into 1 minute, which produces the final result of 38 minutes, 45 seconds, and 0.0005 of a second. The result of any calculation has the same precision as the original INTERVAL operand.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]