How do I insert TIMESTAMP WITH TIME ZONE into a table without converting the value ot UTC.

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

How do I insert TIMESTAMP WITH TIME ZONE into a table without converting the value ot UTC.

Hi

I created a table with a single column of TIMESTAMP(0) WITH TIME ZONE. When I try to insert value with the following statement

INSERT INTO XOBTTEST (timestmp_with_zone) VALUES ('2010-10-20 14:23:00-05:00' (TIMESTAMP(0) WITH TIME ZONE,FORMAT 'YYYY-MM-DDbHH:MI:SSZ');

I got this error:
3707: Syntax error, expected sothing like ')' or ',' between ")" and the "WITH" keyboard.

When I try the following statement:

INSERT INTO XOBTTEST (timestmp_with_zone) VALUES ('2010-10-20 14:23:00-05:00' (TIMESTAMP(0) ,FORMAT 'YYYY-MM-DDbHH:MI:SSZ');

and run select from the table, it converted the value to the UTC value with an offset of +00:00.

My question is how can I enter a value using the FORMAT clause to a TIMESTAMP(0) WITH TIME ZONE without converting the value to UTC?
1 REPLY
jim
Teradata Employee

Re: How do I insert TIMESTAMP WITH TIME ZONE into a table without converting the value ot UTC.

Teradata will always convert a time with a time zone to your current session time zone. If you run a "Help Session" you will see that it is +00:00. So if you add in a timestamp with a -5 time zone, Teradata will convert that to the time at 00:00 and store that value. You can change your session time zone by issuing the command "SET Time Zone Interval - '08:00' hour to minute;" or "Set Time Zone Interval + '08:00' hour to minute;" The first would set your time zone to -8 and the second would set it to +8. All dates you then insert into a table will be adjusted based on the -08:00 or +08:00 versus the 00:00. If you need to keep a datetime in some visual format, keep it as a varchar or split out the timezone into a different field. If you keep it as the first, you could then cast it as needed for calculations.