TdPeriodTimestampWithTimeZone Structure

TdPeriodTimestampWithTimeZone is a .Net Data Provider for Teradata specific type that represents a duration of time that is bound by beginning and ending timestamps that have a time zone.

Object Model

Syntax

Visual Basic
C#
C++/CLI

'Declaration
 
Public Structure TdPeriodTimestampWithTimeZone 
   Inherits System.ValueType
   Implements System.Data.SqlTypes.INullable, System.IComparable, System.IComparable(Of TdPeriodTimestampWithTimeZone), System.IConvertible, System.IEquatable(Of TdPeriodTimestampWithTimeZone), System.IFormattable, System.Xml.Serialization.IXmlSerializable

'Usage
 
Dim instance As TdPeriodTimestampWithTimeZone

public struct TdPeriodTimestampWithTimeZone : System.ValueType, System.Data.SqlTypes.INullable, System.IComparable, System.IComparable<TdPeriodTimestampWithTimeZone>, System.IConvertible, System.IEquatable<TdPeriodTimestampWithTimeZone>, System.IFormattable, System.Xml.Serialization.IXmlSerializable

public value class TdPeriodTimestampWithTimeZone : public System.ValueType, System.Data.SqlTypes.INullable, System.IComparable, System.IComparable<TdPeriodTimestampWithTimeZone>, System.IConvertible, System.IEquatable<TdPeriodTimestampWithTimeZone>, System.IFormattable, System.Xml.Serialization.IXmlSerializable

Remarks

The Advanced SQL Engine introduced the Period type in release 13.0. Unfortunately, the .Net Framework does not contain a system type that corresponds to the Teradata Period(TimestampWithTimeZone) type. Therefore, the Teradata provider exposes the TdPeriodTimestampWithTimeZone type which enables an application to work with data of type Period(TimestampWithTimeZone).

Similar to TdTimestampWithTimeZone, TdPeriodTimestampWithTimeZone supports a scale for the sub-seconds and a UTC offset. The scale ranges from 0 to 6 and must be the same for both the beginning and ending bounds.

A period is an anchored duration. It represents a set of contiguous time granules within that duration. In the case of TdPeriodTimestampWithTimeZone, the granularity of the time can range from 10^-6 (.000006 second) to 10^0 (1 second). The representation of a period is both inclusive and exclusive. It is inclusive in that the duration of the period begins from the beginning bound up to, but not including (excluding), the ending bound. For example suppose a TdPeriodTimestampWithTimeZone contains the period of:

    "(2008-10-31 13:12:21.05+08:00, 2008-12-01 20:43:27.98+08:00)"

The duration will include "2008-10-31 13:12:21.05+08:00" and not "2008-12-01 20:43:27.98+08:00". It has a duration of 32.07:31:06.93 (32 days, 7 hours, 31 minutes, 6 seconds, 980000 microseconds). The time element "2008-11-30 15:21:43+08:00" is included in the period.

The components of a period consists of the following:

Time Granule This is the minimum representation of time within a period. In the above example, the time granule is 10^-2 (.01 second).
Time Element A Time value that is contained within the period. The time element in TdPeriodTimestampWithTimeZone is specified using a TdTimestampWithTimeZone.
Beginning Bound The lower bound or the start of the period. Within a period, this value represents the "lowest" time element. The beginning bound must be less than the ending bound.
Ending Bound The upper bound or the ending of the period. The Ending Bound is not included in the period. Therefore, No Time Element in the period can be greater or equal to the Ending Bound.
Duration The number of granules in a period. It is represented as an interval/timespan.

The UTC Offset of the beginning and ending bounds do not have to be the same. However, the UTC representation of the beginning bound must be less than the UTC representation of the ending bound.

A TdPeriodTimestampWithTimeZone can also be specified as an In, Out, or InOut parameter to a Stored Procedure. The data will be returned to an application using either the TdParameter.ProviderSpecificValue or TdParameter.Value property. Retrieving the parameter using TdParameter.ProviderSpecificValue, will return the data using the Period structure. When TdParameter.Value is used, the data is returned as a String (this is the corresponding .Net type).

Example

The following is an example on retrieving a TdPeriodTimestampWithTimeZone, modify the period, and then updating the period.

Public void PeriodDateExample(TdCommand cmd, String studentId)
{
   cmd.Parameter.Clear();
             
   cmd.CommandText = "SELECT timePeriod  " +
                   "FROM ClassDetention " +
                   "WHERE StudentId = ?";
             
   cmd.CommandType = CommandType.Text;
             
   // creating the parameter
   cmd.Parameters.Add(null, TdType.Varchar, 9,
      System.Data.ParameterDirection.Input, true, 0, 0, null,    
      System.Data.DataRowVersion.Default, studentId);
             
   TdDataReader dr = null;
 
   Int32 i = 0;
             
   TdPeriodTimestampWithTimeZone detention;
             
   Try
   {
      dr = cmd.ExecuteReader();
             
      If (false == dr.Read())
      {
	       return;
      }
   
      // Retrieving the Period
      detention = dr.GetTdPeriodTimestampWithTimeZone(0);
   }
   finally
   {
      if (dr != null)
      {
         dr.Close();
      }
   }
             
   // An additional 1 day, 4 hours will be added to ending bound.
             
   // Specifying a TimeSpan of 1 hour
   System.TimeSpan extraDentention = new TimeSpan(1, 4, 0, 0);
             
   // Getting the ending bound of the period and
   // adding 1 hour to it
   detention = new TdPeriodTime(detention.Begin, detention.End + extraDetention);    
             
   cmd.Parameters.Clear();
             
   cmd.CommandText = "UPDATE classDetention " +
                        "SET timePeriod = ? " +
                        "WHERE StudentId = ?";
             
   cmd.Parameters.Add(null, TdType.PeriodTimestampWithTimeZone, 0,
         System.Data.ParameterDirection.Input, true, 0, 0, null,    
         System.Data.DataRowVersion.Default, detention);
             
   cmd.Parameters.Add(null, TdType.Varchar, 9,
         System.Data.ParameterDirection.Input, true, 0, 0, null,    
         System.Data.DataRowVersion.Default, studentId);
             
   cmd.ExecuteNonQuery();
}

Inheritance Hierarchy

System.Object
System.ValueType
Teradata.Client.Provider.TdPeriodTimestampWithTimeZone

Requirements

Target Platforms: Windows 8.1, Windows 10, Windows Server 2012 R2, Windows Server 2016, Windows Server 2019

Reference

TdPeriodTimestampWithTimeZone Members
Teradata.Client.Provider Namespace
TdTimestamp Structure
Provider Specific Types: Period Type Overview