Teradata timestamp - Part 4 - TIMESTAMP/TIME with Time Zones:

TIMESTAMP/TIME with Time Zones:

Time Zones are always relative to standard accepted base time zone.

Universally the Greenwich mean time is assigned 00:00 and all time zones are expressed relative to this GMT.

We can see the current zone using the inbuilt functions CURRENT_TIME and CURRENT_TIMESTAMP.

On my system when I execute the query I get following result:

SELECT CURRENT_TIME,CURRENT_TIMESTAMP;

Current Time(0)	Current TimeStamp(6)
03:00:02	4/13/2013 03:00:02.026000

I don’t see the Time zone in my result.(this may be specific to my system)  However when I cast the result to characters I get to see the Time Zone

SELECT CAST(CURRENT_TIME AS CHAR(20)),CAST(CURRENT_TIMESTAMP AS CHAR(35))

Current Time(0)	Current TimeStamp(6)
03:03:03+00:00	2013-04-13 03:03:03.820000+00:00

When we want time zone we should use the data type TIME WITH ZONE and TIMESTAMP WITH ZONE .

Both TIME WITH ZONE and TIMESTAMP WITH ZONE may be represented as literals using conventions we have seen previously.

The addition of the time zone adds two bytes of storage to both of these data types,

• one byte for the hour and

• one byte for the minute representation of the time zone.

Following is how we represent TIME WITH ZONE and TIMESTAMP WITH ZONE literals.

Literal	Interpreted data type
TIME '12:12:12-08:11'	select TYPE(TIME '12:12:12-08:11' ) --> TIME(0) WITH TIME ZONE
TIME '12:12:12.11121+02:30'	select TYPE(TIME '12:12:12.11121+02:30' ) --> TIME(5) WITH TIME ZONE
TIME '12:12:12.111216+02:30'	select TYPE(TIME '12:12:12.111216+02:30' ) --> TIME(6) WITH TIME ZONE

Note that when we specify TIME WITH TIMEZONE literal we don’t write the word 'WITH TIMEZONE'

Also there is no space on the with side of + or - sign.

Literal	Interpreted data type
TIMESTAMP '2001-01-01 12:12:11+04:30'	select TYPE(TIMESTAMP  '2001-01-01 12:12:11+04:30')--> TIMESTAMP(0) WITH TIME ZONE
TIMESTAMP '2001-01-01 12:12:11.123555+04:30'	select TYPE(TIMESTAMP '2001-01-01 12:12:11.123555+04:30') --> TIMESTAMP(6) WITH TIME ZONE

Following is the number of bytes taken for internal storage by these data types:

TIME(n) WITH TIME ZONE	6 bytes for TIME + 2 bytes for TIMEZONE TIME --> 6 bytes TIMEZONE_HOUR --> ByteInt , -12 to 13 TIMEZONE_MINUTE --> ByteInt , 00 to 59
TIMESTAMP(n) WITH TIME ZONE	12 Bytes for TIMESTAMP + 2 bytes for TIMEZONE TIMESTAMP --> 10 bytes TIMEZONE_HOUR --> ByteInt , -12 to 13 TIMEZONE_MINUTE --> ByteInt , 00 to 59

The valid range of time zones is from -12:59 to 13:00.