This period datatype is similar to PERIOD(TIMESTAMP), with the addition of a time zone.
Only character host variable formats are supported. Two TIMESTAMP WITH TIMEZONE members, each with the format shown in Table 7, are supported.
|
Field |
Displacement |
Length |
Description |
|
Seconds |
0 |
4 |
This integer represents the number of seconds as a scaled decimal. For example, 12.56 seconds is returned as 12560000. |
|
Year |
4 |
2 |
This integer represents the year value. |
|
Month |
6 |
1 |
This integer represents the month value. |
|
Day |
7 |
1 |
This integer represents the day of the month. |
|
Hours |
8 |
1 |
This integer represents the number of hours. |
|
Minutes |
9 |
1 |
This integer represents the number of minutes. |
|
Timezone Hour |
10 |
1 |
This integer represents the hours portion of the time zone displacement, and whether the displacement is + or -. A value of 16 represents 0 hours. A value less than 16 represents a negative time zone displacement for the specified hours. For example, if the value is 10, the time zone is displaced by -10 hours. If the value is greater than 16, it specifies a positive time zone displacement of (Time Zone Hour – 16) hours. For example, a value of 20 implies a +4 hour displacement. |
|
Timezone Minute |
11 |
1 |
This byte represents the minutes portion of the time zone displacement. |
The total length of the output is 24 bytes.
Table 4 through Table 7 describe the format of what is returned from the database server when using SELECT.
This is an example of a C program that uses PERIOD(TIME WITH TIMEZONE) and demonstrates how each field is returned. The period data is retrieved from the Teradata Database in binary format.
#include <stdio.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
char Time[16];
short TimeInd;
char LOGON_STR[70];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
struct time
{
SQLInt32 tSeconds;
unsigned char tHour;
unsigned char tMinute;
unsigned char tTZHour;
unsigned char tTZMinute;
};
/**********************************************************************/
/* format time */
/**********************************************************************/
void formatTime(out, in, len)
char* in, *out;
int len;
{
int sec1, hsec1, tzhour;
struct time time1;
char sign;
memcpy(&time1, in, len);
sec1 = time1.tSeconds / 1000000;
hsec1 = time1.tSeconds % 1000000;
if (len == 6) /* timezone not included */
{
sprintf(out, "%02d:%02d:%02d.%04d",
time1.tHour, time1.tMinute,sec1,hsec1);
}
else /* timezone is included */
{
/* can't use both + and 0 sprintf flags, do sign manually */
if (time1.tTZHour >= 16)
{
sign = '+';
time1.tTZHour -= 16;
}
else
sign = '-';
sprintf(out, "%02d:%02d:%02d.%04d%c%02d:%02d",
time1.tHour, time1.tMinute,sec1,hsec1, sign,
time1.tTZHour,time1.tTZMinute );
}
}
/**********************************************************************/
/* main */
/**********************************************************************/
main ()
{
/* assuming a table created similar to
CREATE TABLE tab01
(
...
PeriodTZ PERIOD(TIME WITH TIME ZONE)
...
) ;
*/
strcpy(LOGON_STR,"mydbs/myuser,mypw");
EXEC SQL
LOGON :LOGON_STR;
EXEC SQL DECLARE CURa CURSOR FOR
SELECT PeriodTZ from tab01;
EXEC SQL OPEN CURa;
while (SQLCODE == 0)
{
EXEC SQL FETCH CURa
INTO :Time :TimeInd;
if (SQLCODE == 0)
{
char t1[8], t2[8];
if (TimeInd == -1)
printf("\nTime is Null");
else
{
formatTime(t1, Time, 8);
printf("\nTime 1 = %s", t1);
formatTime(t2, Time+8, 8);
printf("\nTime 2 = %s", t2);
}
}
}
EXEC SQL
LOGOFF;
}
This example’s use of Teradata casting operators is an easier way to achieve character formatting and retrieve period data from the Teradata Database than “Example 1.”
CREATE TABLE tab02
(
MyKey int,
PeriodD PERIOD(DATE),
PeriodT PERIOD(TIME)
) ;
INSERT INTO tab02 VALUES (1,
PERIOD(CAST('2005-02-13' AS DATE), CAST('2006-02-13' AS DATE)),
PERIOD(TIME '12:00:00', TIME '13:59:59'));
SELECT PeriodT (CHARACTER(38)) FROM tab02;
Returns: ('12:00:00.000000', '13:59:59.000000')
This example uses casting operators to convert character datatypes to period datatypes when inserting data into the Teradata Database.
EXEC SQL CREATE TABLE tab01
(
Key INTEGER,
PeriodD PERIOD(DATE),
PeriodT PERIOD(TIME),
PeriodTZ PERIOD(TIME WITH TIME ZONE),
PeriodTS PERIOD(TIMESTAMP),
PeriodTSZ PERIOD(TIMESTAMP WITH TIME ZONE)
);
char TIME1[9], TIME2[9];
short TIMEIND;
char TIMESTAMPZ1 [29], TIMESTAMPZ2 [29];
strcpy(TIME1,”12:00:00”);
strcpy(TIME2,”13:00:00”);
strcpy(TIMESTAMPZ1,”2013-01-02 12:00:00.33-01:00”);
strcpy(TIMESTAMPZ2,” 2013-02-03 13:59:59.44-01:00”);
EXEC SQL INSERT INTO tab01 VALUES (1,
PERIOD(DATE '1900-07-07', DATE '1900-07-27'),
PERIOD(TIME :TIME1, TIME :TIME2) :TIMEIND,
PERIOD(TIME '01:02:03.400+01:00', TIME '02:48:48.400+01:00'),
PERIOD(TIMESTAMP '2013-06-27 01:02:03.400', TIMESTAMP '2013-07-04 13:14:15.16'),
PERIOD(TIMESTAMP :TIMESTAMPZ1, TIMESTAMP : TIMESTAMPZ2)
);
For an example using descriptors, see “Period Datatype Example Using Descriptor” on page 137.
For COBOL examples, see “Period Datatype Examples” on page 179.