Time Expressions

A time expression is a specification that INFORMIX-4GL can evaluate as a DATE, DATETIME, or INTERVAL value.

As the diagram suggests, the DATE data type is a logical subset of DATETIME. 4GL rules for arithmetic, however, are not identical for DATE and DATETIME operands, and formatting features like USING or the FORMAT and PICTURE attributes treat DATETIME and DATE values differently.

The time data types are logically related because they express values as units of time. But unlike the number data types or the character data types, within which 4GL generally supports automatic data type conversion (aside from restrictions based on truncation, overflow, or under flow), conversion among time data types is more limited. In contexts where a time expression is required, DATETIME or DATE values can sometimes be substituted for one another. INTERVAL values, however, which represent one-dimensional spans of time, cannot be converted to DATETIME or DATE values, because these represent zero-dimensional points in time.

In addition, if the declared precision of an INTERVAL value includes years or months, then automatic conversion to an INTERVAL having smaller time units (like days, hours, minutes, or seconds) is not available.

Each of the three types of time expressions has its own syntax:

format string is a quoted character string to specify a DATE display format.

keyword is YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, or FRACTION.

In each case, the function call or named value must return a single value of the corresponding data type.

Numeric Date

A numeric date represents a DATE value as a quoted string of digits:

DATETIME Qualifier

The DATETIME qualifier specifies the precision and scale of a DATETIME value. It has the same syntax as for DATETIME database columns:

scale is a whole number from 1 to 5, enclosed between parentheses. This specifies the number of decimal digits for fractions of a second. If you omit the scale specification, the default scale is 3 digits.

DATETIME Literal

A DATETIME literal is the representation of a DATETIME value as the numeric date and time, or a portion thereof, followed by a DATETIME qualifier:

yyyy is a number from 1 to 9999, representing a year. If you use only two digits, 19 is assumed as the first part of the year, as in 1993.

mo is a number from 1 to 12, representing a month.

dd is the number of the day of the month, from 1 to 31.

space is a blank space (ASCII 32), entered by pressing the SPACEBAR.

hh is the hour (from a 24-hour clock), from 0 (= midnight) to 23.

mi is the minute of the hour, from 0 to 59.

ss is the second of the minute, from 0 to 59.

fffff is the fraction of a second, in up to 5 digits, as set by the precision specified for the FRACTION time units in the DATETIME qualifier.

INTERVAL Qualifier

The INTERVAL qualifier specifies the precision and scale of an INTERVAL value. It has the same syntax in 4GL as for INTERVAL database columns:

precision is the number of digits in the largest number of months, days, hours, minutes, or seconds that the interval can include. The default number of digits is two; the maximum is nine.

scale is the number of decimal digits to record fractions of a second in a span of time. The default is three digits; the maximum is five.

y- precision is the number of digits in the largest number of years that the interval can include. The default is four; the maximum is nine.

INTERVAL Literal

An INTERVAL literal represents a span of time as a numeric representation of its chronological units, followed by an INTERVAL qualifier.

dd is the number of days.

fffff is the fraction of a second in up to five digits, depending on the precision of the fractional portion in the INTERVAL qualifier.

hh is the number of hours.

mi is the number of minutes.

mo is the number of months, in two digits.

space is a blank space (= ASCII 32), made by pressing the spacebar.

ss is the number of seconds.

yyyy is the number of years.

Arithmetic Operations on Time Values

Time expressions can be operands of some arithmetic operators. These expressions return a DATE value:

Here you can also reverse the data types of the operands. All the other binary arithmetic operators also accept DATE operands, equivalent to the count of days since December 31, 1899, but the values returned (except from a DATE expression as the left-hand MOD operand) are meaningless in most applications.

NOTE: DATE and DATETIME values have no true zero point, so they lie on "interval" scales. Such scales support addition and subtraction, as well as relational operators, but multiplication, division, and exponentiation are undefined.

The difference between two DATE values is an INTEGER value, representing the positive or negative number of days between the two calendar dates.

You must explicitly apply the UNITS DAY operator to the difference between DATE values, if you wish to store the result as an INTERVAL value.

This is the syntax for arithmetic expressions that return a DATETIME value:

Do not write expressions that specify the sum ( + ) of two DATE or DATETIME values, nor a difference ( - ) whose second operand is a DATE or DATETIME value, and whose first operand is an INTERVAL value.

This is the syntax for arithmetic expressions that return an INTERVAL value: