Interval data types

The system supports the unloading and loading of intervals by using a non-standard interval data type.

Syntax

[ [ '-' ] <digit>+ ' '*	'y'['e'['a'['r'['s']]]] ' '* ]
[ [ '-' ] <digit>+ ' '*	'm'['o'['n'['t'['h'['s']]]]] ' '* ]
[ [ '-' ] <digit>+ ' '*	'd'['a'['y'['s']]] ' '* ]
[ [ '-' ] <time> ]

Description

Optional elements are enclosed in brackets.
Literal characters are enclosed in single quotation marks.
* indicates zero or more.
+ indicates one or more.
<digit> is an instance of one of the 10 decimal digits 0 - 9.
<time> is an instance of the loader's time syntax.

Limitations

The maximum year value is 0x7fffffff or 12 (178956970 in decimal).
The maximum month value is 0x7fffffff (2147483647 in decimal).
The maximum day value is 0x7fffffffffffffff or (1000000 x 60 x 60 x 24) (106751991 in decimal).
The maximum hour value is 0x7fffffffffffffff or (1000000 x 60 x 60) (2562047788 in decimal).
The maximum minute value is 60 in decimal.
The maximum second value is 60 in decimal.

The Netezza Performance Server unload interval format has the following behaviors:

It accepts the interval unit specification for a column but internally ignores it. An example follows:
```
create external table tablename (colA interval year to month, colB interval hour)
```
All intervals are the same and can contain values of any combination of units.
The valid units are year, month, day, hour, minute, and second, in lowercase and singular or plural forms. Examples follow:
```
2 year 3 month 4 day
2 years 3 months 4 days
```
All literal values except the interval unit hour, minute, and second include the units. An example follows:
```
1 year 1 month 1 day
```
The time interval for the unit hour, minute, and second is in conventional decimal temporal format and is in 24HOUR style. Examples follow:
```
01:01:01
01:01:01.1
```
All literal values can be positive and negative. Examples follow:
```
-1 year
1 year
01:01:01
-01:01:01  
```
There can be zero or more leading and trailing spaces in the interval string field. Following are valid sample values with leading or trailing spaces (shown between field delimiters for clarity):
```
|1 year 1 day|
|     2 years 2 months    |
|3 days 03:03:03.333|
```