15.10 - PERIOD(TIMESTAMP WITH TIMEZONE) - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

prodname
Preprocessor2 for Embedded SQL
vrm_release
15.10
category
Programming Reference
featnum
B035-2446-035K

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.

 

Table 7: PERIOD(TIMESTAMP WITH TIMEZONE) MEMBERS

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.