SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—544536-007
7-29
Managing Table Data
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:
•
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:
•
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:
•
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.
COLUMN_1 INTERVAL YEAR
COLUMN_2 INTERVAL HOUR (3)
COLUMN_3 INTERVAL YEAR TO MONTH
COLUMN_4 INTERVAL DAY TO MINUTE
DEFAULT interval-literal A valid INTERVAL literal
DEFAULT NULL Initialized to the null value
First Operand Operator
Second
Operator Result
Datetime - Datetime INTERVAL
Datetime + or - INTERVAL Datetime
INTERVAL + Datetime Datetime
INTERVAL + or - INTERVAL INTERVAL
INTERVAL * or / Numeric INTERVAL
Numeric * INTERVAL INTERVAL
INTERVAL / INTERVAL Numeric