SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

Guidelines for Date and Time Interval Data
When you define a column to hold date and time, date, time, or time interval values, use these
general guidelines:
• A column of the DATE, TIME, or TIMESTAMP type holds a value that represents a date or an
instant in time, and a column of the INTERVAL type holds a value that represents a time interval,
or duration.
• The range of fields defined for an INTERVAL column can limit the value stored, as shown:
INTERVAL YEARCOLUMN_1
INTERVAL HOUR (3)COLUMN_2
INTERVAL YEAR TO MONTHCOLUMN_3
INTERVAL DAY TO MINUTECOLUMN_4
• The fields in a datetime or INTERVAL value have this implied order: YEAR, MONTH, DAY,
HOUR, MINUTE, SECOND.
• Possible default values for an INTERVAL column are:
A valid INTERVAL literalDEFAULT interval-literal
Initialized to the null valueDEFAULT NULL
• You cannot use a datetime column with other SQL/MX data types in arithmetic expressions
or comparisons, except for interval or datetime data types. This table shows the results of
arithmetic operations involving datetime and interval values:
ResultSecond OperatorOperatorFirst Operand
INTERVALDatetime-Datetime
DatetimeINTERVAL+ or -Datetime
DatetimeDatetime+INTERVAL
INTERVALINTERVAL+ or -INTERVAL
INTERVALNumeric* or /INTERVAL
INTERVALINTERVAL*Numeric
NumericINTERVAL/INTERVAL
• A negative value is not a valid entry for a datetime column. An interval column, however, can
contain negative values.
• Interval values can be multiplied or divided by positive or negative numeric values.
• The result of adding or subtracting two INTERVAL values is an INTERVAL value.
• The result of adding or subtracting an INTERVAL value to or from a datetime value is a datetime
value.
• Carries and borrows can occur when adding or subtracting an INTERVAL value to or from a
datetime value. For example, subtracting INTERVAL ‘1’ MONTH from TIMESTAMP ‘2004-01-01
12:00:00’ results in TIMESTAMP ‘2003-12-31 12:00:00’. That is, the month field is changed
and a borrow occurs from the year field.
• If an arithmetic expression causes invalid data, an SQL/MX error is generated.
Creating SQL/MX Tables 93