SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-25
Defining Columns
•
A column defined as DATETIME can have a range of DATETIME fields to limit the
set of values stored:
COLUMN_1 DATETIME YEAR TO SECOND
COLUMN_2 DATETIME MONTH TO DAY
COLUMN_3 DATETIME HOUR TO MINUTE
•
A date-time column stores values in local civil time (LCT). The LCT is determined
by the node where the SQL executor is running, based upon the TIME ZONE
OFFSET and DAYLIGHT SAVINGS parameters established for the system during
system generation.
•
The range of fields defined for an INTERVAL column can limit the value stored; for
example:
COLUMN_1 INTERVAL YEAR
COLUMN_2 INTERVAL HOUR(3)
COLUMN_3 INTERVAL YEAR TO MONTH
COLUMN_4 INTERVAL DAY TO MINUTE
•
The fields in a date-time or INTERVAL value have this implied order: YEAR,
MONTH, DAY, HOUR, MINUTE, SECOND, FRACTION.
•
Possible default values for a DATETIME column are:
DEFAULT date-time-literal A valid date-time literal
DEFAULT SYSTEM Initialized to the current
timestamp
DEFAULT CURRENT Initialized to the current
timestamp
DEFAULT NULL Initialized to the null value
•
Possible default values for an INTERVAL column are:
DEFAULT interval-literal A valid INTERVAL literal
DEFAULT SYSTEM Initialized to zero
DEFAULT NULL Initialized to the null value
•
A date-time column cannot be used with other SQL data types in arithmetic
expressions or comparisons, except with INTERVAL data types. This table shows
the results of arithmetic operations involving date-time and INTERVAL values:
•
A negative value is not a valid entry for a date-time column. An INTERVAL column,
however, can contain negative values.
First Operand Operator Second Operator Result
Date-time - Date-time INTERVAL
Date-time + or - INTERVAL Date-time
INTERVAL + Date-time Date-time
INTERVAL + or - INTERVAL INTERVAL
INTERVAL * or / Scalar INTERVAL
Scalar * INTERVAL INTERVAL
INTERVAL / INTERVAL Numeric