Dynamically update timezone.

UDA

Dynamically update timezone.

Hi,
We deal with multiple sources and the data comes from different countries across the globe.
I would like to store the data in one timezone say CET.

Is there any way I can setup my database to accept all timezones, dynamically change the data to CET and store.

4 REPLIES
Teradata Employee

Re: Dynamically update timezone.

Best solution may be to use TIMESTAMP WITH TIME ZONE datatypes and explicitly provide the appropriate time zone offset as part of the input. Alternatively if all data for a particular session comes from a single time zone you can SET SESSION TIME ZONE to an approprate INTERVAL HOUR TO MINUTE value; in effect the input will be converted from session time zone to UTC within the database.

In either case, you can later display the timestamp value relative to current session time zone (using CAST to TIMESTAMP without time zone if your field was stored WITH TIME ZONE).

Using separate TIME and DATE fields exposes you to issues when applying a time zone offset crosses midnight; it's much simpler and safer to use TIMESTAMP rather than TIME (even if you also have a separate DATE field for other reasons).
Enthusiast

Re: Dynamically update timezone.

Anybody have experience loading timestamp WITH TIME ZONE columns from an input file with the common load utilities (FAST, MULTI)?
Enthusiast

Re: Dynamically update timezone.

Can one simply load a TIMESTAMP with TIME ZONE column with a format of "YYYY-MM-DD HH:MM:SS.(n) -07:00" from a vartext formatted file with Fastload?
Senior Apprentice

Re: Dynamically update timezone.

Hi Dave,
all TIME/TIMEZONE/INTERVAL datatypes must be defined as (VAR)CHAR:

.define
ts varchar(xx)

If the data doesn't match the format of the target column you have to typecast using an appropriate format in the insert statement:
:ts (timestamp, format 'yyyy-mm-ddBhh:mi:ssDs(F)BZ')

Dieter