CODEWITHSUNDEEP
teradata
user2711819
user2711819

Reputation: 960

Teradata 14: Timestamp with timezone issue

I want to load data.txt using MLOAd

data.txt

1,20030101020000.000000,20030101020000.000000 -08:00

Table def:

create table  test
( sno varchar(3),
  ts_name TIMESTAMP(6) ,
  ts_zonename TIMESTAMP(6) WITH TIME ZONE
);

Mload insert :

insert into test (sno,ts_name,ts_zonename) 
   values (:in_sno,
           CAST(:in_ts_name AS TIMESTAMP(     6)  FORMAT 'YYYYMMDDHHMISSDS(     6)'),
           CAST(:in_ts_zonename   AS TIMESTAMP(     6)  FORMAT  'YYYYMMDDHHMISSDS(     6)')
                     );

mload success .

When i Query 

SEL EXTRACT(TIMEZONE_HOUR FROM ts_zonename) 
        FROM test;

through BTEQ

it returns 0

Shouldn't it return -8 ?

I am on Teradata 14

Upvotes: 0

Views: 4178

Answers (1)

dnoeth
dnoeth

Reputation: 60482

You CASTed to a TIMESTAMP which dropped the time zone, check the actual data inserted into ts_zonename.

You need to cast to a TIMESTAMP WITH TIME ZONE:

CAST(:in_ts_zonename   AS TIMESTAMP(     6) WITH TIME ZONE  FORMAT  'YYYYMMDDHHMISSDS(F)Z')

And if there's an space before the time zone you need to change the FORMAT to 'YYYYMMDDHHMISSDS(F)BZ' or switch to Oracle's TO_TIMESTAMP_TZ in TD14, which is a bit more flexible regarding missing/additional blanks:

TO_TIMESTAMP_TZ(ts_zonename,  'YYYYMMDDHH24MISS.FF6TZH:TZM')

Upvotes: 2

Related Questions