16.20 - PERIOD (DATE) / PERIOD(TIME) / PERIOD(TIMESTAMP) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

C Data Type Definition

typedef int PDT_HANDLE;

Usage

Use the PDT_HANDLE data type when passing or returning a PERIOD(DATE), PERIOD(TIME(n)), PERIOD(TIME(n) WITH TIME ZONE), PERIOD(TIMESTAMP(n)), or PERIOD(TIMESTAMP(n) WITH TIME ZONE)) argument or return type.

The fractional seconds precision defined for the SQL PERIOD(TIME(n)), PERIOD(TIME(n) WITH TIME ZONE), PERIOD(TIMESTAMP(n)), or PERIOD(TIMESTAMP(n) WITH TIME ZONE)) type applies to the input arguments and return argument of a function. A value outside the valid range of values produces an error.

All PERIOD(TIME(n)) and PERIOD(TIMESTAMP(n)) values passed to a UDF are in Universal Coordinated Time (UTC), and all PERIOD(TIME(n)) and PERIOD(TIMESTAMP(n)) values that are returned from a UDF must be in UTC.

The PERIOD(TIME(n) WITH TIME ZONE) and PERIOD(TIMESTAMP(n) WITH TIME ZONE) types have time zone fields, zone_hour and zone_minute, that have an internal form and an external form. When input arguments with these types are passed to a UDF, the values must be in internal form. Similarly, return arguments with these types must be in internal form.

To modify the time zone fields of the PERIOD(TIME(n) WITH TIME ZONE) and PERIOD(TIMESTAMP(n) WITH TIME ZONE) types, use the following macros in the sqltypes_td.h file to convert between the internal and external representation of the time zone fields of ANSI_Time_Wzone and ANSI_TimeStamp_Wzone.
  • TIMEZONE_INTERNAL_TO_EXTERNAL(i,s,h,m)

    where:

    Input Parameter Description
    i a pointer to an ANSI_Time_Wzone or a pointer to an ANSI_TimeStamp_Wzone.
    s a BYTEINT value to store the sign of i.
    h a BYTEINT value to store the hour offset of i.
    m a BYTEINT value to store the minute offset of i.
  • TIMEZONE_EXTERNAL_TO_INTERNAL(s,h,m,i)

    where:

    Input Parameter Description
    s a BYTEINT value to set the sign for i.
    h a BYTEINT value to set the hour offset for i.
    m a BYTEINT value to set the minute offset for i.
    i a pointer to an ANSI_Time_Wzone or a pointer to an ANSI_TimeStamp_Wzone.
The external representation of the time zone fields consists of 3 values: a sign, an hour offset, and a minute offset where:
  • The sign is a BYTEINT value which is 0 if the offsets are negative and 1 if the offsets are positive.
  • The external form for the hour offset is a BYTEINT value between 0 and 14 inclusive prior and post conversion. This represents the hour offset between UTC and the given time zone.
  • The external form for the minute offset is a BYTEINT value between 0 and 59 inclusive prior and post conversion. This represents the minute offset between UTC and the given time zone.

The largest value for negative offsets is -12:59. The largest value for positive offsets is +14:00.

This representation results in two representations of UTC, that is +00:00 and -00:00. This mirrors the internal representation which uses both representations for display purposes.

For details on Period types, see Teradata Vantage™ - Data Types and Literals, B035-1143.

Here is an example using a PERIOD(DATE) in a UDF definition and PDT_HANDLE in a C function declaration.

SQL Function Definition Equivalent C Function Declaration
CREATE FUNCTION F1 (
  A PERIOD(DATE) )
RETURNS PERIOD(DATE)
 ...;
void f1( PDT_HANDLE *a,
         PDT_HANDLE *result,
   ... )
{  ... }