Each of the Period types support the addition and subtraction of a System.TimeSpan. The operation will be performed on both the beginning and ending bounds of the Period.
TdPeriodTime and TdPeriodTimeWithTimeZone supports addition and subtraction of only the Day-Time Interval Provider Specific types. The arithmetic operation will be performed on both the beginning and ending bounds of the period. Any day components of the Day-Time Interval types will be ignored in these operations. These Day-Time Interval types include:
The Period type operators for TdPeriodTime and TdPeriodTimeWithTimeZone define arithmetic support for the Interval TdIntervalDayToSecond. But implicit conversion of the other Day-Time Interval types to TdIntervalDayToSecond provides support for these other types. For a list of the Interval implicit conversion type support, please see Interval Implicit Conversions.
The Year-Month Interval types include:
TdPeriodDate, TdPeriodTimestamp and TdPeriodTimestampWithTimeZone supports addition and subtraction of both the Day-Time Interval Provider Specific types and the Year-Month Interval Provider Specific types. The arithmetic operation will be performed on both the beginning and ending bounds of the period. Only the day component of the Day-Time Interval types will be considered in arithmetic operations with TdPeriodDate.
The Period type operators for TdPeriodDate, TdPeriodTimestamp and TdPeriodTimestampWithTimeZone define arithmetic support for the Interval TdIntervalDayToSecond and the Interval TdIntervalYearToMonth. But implicit conversion of the other Day-Time Interval types to TdIntervalDayToSecond and the other Year-Month Interval types to TdIntervalYearToMonth provides support for these other types. For a list of the Interval implicit conversion type support, please see Interval Implicit Conversions.
When an arithmetic operation is performed on a TdPeriodTime or a TdPeriodTimeWithTimeZone, it is possible that the time for either the beginning or ending bounds will roll to the previous or next 24 hour. If this occurs and causes the beginning bound to be greater than or equal to the ending bound an OverflowException will be thrown. For example if a time span of 2 hours is added to the period
(12:32:22.00, 23:10:11.00)
the time for the ending bound will roll to the next 24 hours. The result will be
(14:32:22.00, 01:10:11.00)
This is an invalid period --the beginning must be less than the ending bound. In this situation, an OverflowException will be thrown by the provider.
When specifying periods within the Teradata Database, the keyword UNTIL_CHANGED can be specified as the ending bound of a period. This signifies that the period goes on forever or until it is changed.
UNTIL_CHANGED can be specfied as the end bound of periods of type Date, Timestamp and Timestamp With Time Zone. The equivalent value for UNTIL_CHANGED is the maximum value that can be specified for the end bound. The following are the equivalent value for each of the period types:
| Provider Type | UNTIL_CHANGED Equivalent Value |
|---|---|
| Period(Date) | 9999-12-31 |
| Period(Timestamp) | 9999-12-31 23:59:59.<sub-seconds> |
| Period(Timestamp With Time Zone) | 9999-12-31 23:59:59.<sub-seconds>+00:00 |
The sub-seconds is based on the scale of the period type. For example, if a period of type timestamp has a scale of two, the equivalent UNTIL_CHANGED value is "9999-12-31 23:59:59.99".
When an arithmetic operation is performed on a period that has an ending bound that is either UNTIL_CHANGED or its equivalent value, the ending bound is unchanged. The following examples use a period of type date to demonstrate this behavior:
| Arithmetic Operation | Result | Comments |
|---|---|---|
| (2010-12-01, 2011-01-01) + "1 day" | (2010-12-02, 2011-01-02) | 1 day is added to both the begin and end bounds. |
| (9900-01-15, UNTIL_CHANGED) + "5 days" | (9900-01-20, UNTIL_CHANGED) | 5 days is added to the begin bound. The end bound is unchanged. |
| (9900-01-15, 9999-12-31) + "10 days" | (9900-01-25, 9999-12-31) | 9999-12-31 is the equivalent value to UNTIL_CHANGED. The end bound is unchanged. |
Refer to the Teradata Database SQL Reference: Data Types and Literals for more information.
The UNTIL_CHANGED keyword is not recognized by the Provider Specific Period Types.
However, if the value of the end bound is equivalent to the maximum value that can be specified, the Period Types will have the same behavior as Teradata when performing arithmetic operations. Only the period types TdPeriodDate, TdPeriodTimestamp, and TdTimestampWithTimeZone are affected by this behavior.
The following is an example of Arithmetic operations:
| C# |
Copy Code
|
|---|---|
System.TimeSpan daysToAdd = new TimeSpan(10, 12, 0, 0); //10 days, 12 hours
TdPeriodTimestamp advertisePeriod =
new TdPeriodTimestamp(
new TdTimestamp(2008, 10, 31, 00, 00, 00, 000000, 4)),
new TdTimestamp(2008, 12, 31, 00, 00,00, 000000, 4));
TdPeriodTimestamp newPeriod = advertisePeriod + daysToAdd;
// Adding 10 days, 12 hours. Result is (2008-11-10 12:00:00.0000, 2009-01-11 12:00:00.0000)
Console.WriteLine(The result of adding 10 days, 12 hours to the advertise period is: {0}", newPeriod);
//Change the end bound of the advertise period to the UNTIL_CHANGED equivalent of 9999-12-31 23:59:59.9999
advertisePeriod = new TdPeriodTimestamp(
new TdTimestamp(2008, 10, 31, 00, 00, 00, 000000, 4)),
new TdTimestamp(9999, 12, 31, 23, 59, 59, 999900, 4));
newPeriod = advertisePeriod + daysToAdd;
// Adding 10 days, 12 hours. Result is (2008-11-10 12:00:00.0000, 9999-12-31 23:59:59.9999)
Console.WriteLine(The result of adding 10 days, 12 hours to the advertise period is: {0}", newPeriod);
|
|