Interval - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.5
Published
February 2018
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
qhj1503087326201.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software
The Interval literal differs significantly depending on whether you are connected to the Teradata and Aster database.

Teradata Database

An Interval literal may take 13 different forms as shown in the examples outlined below. An Interval literal represents a duration of time that may consist of counts of one or more adjacent units of time, including Years, Months, Days, Hours, Minutes and Seconds.
Years and Months may not be mixed with Hours, Minutes and Seconds.
Seconds may optionally include up to 6 digits of decimal precision. The overall value of an Interval may be negative, but not the individual fields.
Interval Literal Examples
Interval Literal Examples
INTERVAL -'1' YEAR
INTERVAL '1-01' YEAR TO MONTH
INTERVAL '1' MONTH
INTERVAL '1' DAY
INTERVAL '1 01' DAY TO HOUR
INTERVAL '1 01:01' DAY TO MINUTE
INTERVAL -'1 01:01:01.1' DAY TO SECOND
INTERVAL '1' HOUR
INTERVAL '1:01' HOUR TO MINUTE
INTERVAL '1:01:01.1' HOUR TO SECOND
INTERVAL '1' MINUTE
INTERVAL '1:01.1' MINUTE TO SECOND
INTERVAL -'1.123456' SECOND
The count of the leading unit of time for an Interval may contain up to 4 digits, while the remaining units must contain at most 2 digits. If seconds are included, up to 6 fractional digits may also be entered. Interestingly, a field other than the leading field for an Interval may contain a value larger than normally associated with the unit of time it represents in a Time literal, such as 99 minutes, but if the interval is converted to a character value, it will display as the remaining value when 1 is added to the next higher field, which in this case would be as 39 with 1 added to the hour field.
Adding up the fields after converting them to the type of the leading element must not yield a value that takes more than 4 digits to display, or a SQL error will occur.
Variable Creation > Input > Variables: SQL Elements pane - Literals > Interval Properties (Teradata)

Using the Properties dialog for the Interval SQL element, any of the 13 types of Interval literal can be created. In addition to selecting the type of Interval literal and checking a box labeled Minus if the value is to be negative, the user must enter the value of each interval element appropriate for the selected type. For instance, the INTERVAL YEAR TO MONTH literal requires both a Year count and a Month count. Fields that are left blank on this dialog are converted to zero, and any leading plus ‘+’ sign or leading or trailing filler characters such as spaces are removed. The values entered must be numeric and limited in size as already described.

The Interval literal is not available as a literal parameter, but the Text Literal Parameter can be used as a substitute. Also, if a Variable consists solely of an Interval literal, it cannot be displayed on the results tab (either directly for a Select statement, or via the Load button when displaying the contents of a created table) without first casting the literal to a character type to avoid an error.

Aster Database

An Interval literal represents a duration of time that may consist of counts of one or more adjacent units of time, including Millennia, Centuries, Decades, Years, Months, Weeks, Days, Hours, Minutes and Seconds.
Years and Months may not be mixed with Hours, Minutes and Seconds.
Seconds may optionally include up to 6 digits of decimal precision. The overall value of an Interval may be negative, but not the individual fields.
Variable Creation > Input > Variables: SQL Elements pane - Literals > Interval Properties (Aster)

Using the Properties dialog for the Interval SQL element, the user must enter the value of each interval element appropriate for the selected type. Fields that are left blank on this dialog are converted to zero, and any leading plus ‘+’ sign or leading or trailing filler characters such as spaces are removed. The values entered must be numeric and limited in size as already described. Checking the box labeled Ago sets the value to negative.

The Interval literal is not available as a literal parameter, but the Text Literal Parameter can be used as a substitute. Also, if a Variable consists solely of an Interval literal, it cannot be displayed on the results tab (either directly for a Select statement, or via the Load button when displaying the contents of a created table) without first casting the literal to a character type to avoid an error.