Netezza interval data types are supported in different way. Netezza accepts the interval syntax, but ignores the unit specification. All intervals are the same, and can contain values of any combination of units. Netezza server internally normalizes all intervals to units of seconds. It considers a month to be 30 days for the purposes of interval comparisons and this approximation may lead to inaccuracy.
Netezza Interval Data Types Implementation
IBM Netezza implements the INTERVAL data type in a different way compared to other databases.
- Netezza allows you to specify interval qualifiers such as YEAR, MONTH, and DAY etc but internally it always uses the same base data type, storing values of any combination of units. You cannot distinguish year-month interval and day-time interval with Netezza.
- It is not possible to specify the scale of a Netezza interval. The precision of Netezza interval data types include fraction of seconds with up to 6 significant digits.
- In Netezza, interval literals must include the units like ‘110 days 10 hours 50 minutes’
The main restriction with Netezza interval data types is that, you cannot load them from an external table.
Read:
- Netezza Date Functions and Examples
- Netezza Extract Functions and Examples
- NZ String Functions and Examples
- nzsql Commands and Its Usage
Netezza Interval Data Types Conversion Examples
Add a one day to current date time using interval data types:
TRAINING.ADMIN(ADMIN)=> select now(); now ------------------------------- 2016-09-15 23:52:49.580912-07 (1 row) TRAINING.ADMIN(ADMIN)=> select now()+ interval '1 day'; NEXT_DAY ------------------------------- 2016-09-16 23:53:00.956273-07 (1 row)
Add a 10 hours to current date time using interval data types:
TRAINING.ADMIN(ADMIN)=> select now()+ interval '10 hour'; ADD_HRS ------------------------------- 2016-09-16 09:56:43.061926-07 (1 row)
Add a 10 minutes to current date time using interval data types:
TRAINING.ADMIN(ADMIN)=> select now()+ interval '10 minutes'; ADD_MINS ------------------------------- 2016-09-16 00:07:42.933638-07 (1 row)
Add a 10 seconds to current date time using interval data types:
TRAINING.ADMIN(ADMIN)=> select now()+ interval '10 seconds'; ADD_SECS ------------------------------- 2016-09-15 23:59:01.748474-07 (1 row)
Convert Netezza Interval Data types to Date format:
TRAINING.ADMIN(ADMIN)=> SELECT cast(EXTRACT(year FROM interval '2016 year 2 months 2 days 10 minutes 10 seconds')|| '-'|| EXTRACT(month FROM interval '2016 year 2 months 2 days 10 minutes 10 seconds')|| '-'|| EXTRACT(day FROM interval '2016 year 2 months 2 days 10 minutes 10 seconds') as date ); date ------------ 2016-02-02 (1 row)