Convert incoming timezone {IST/PST/GMT etc..} TS to CST timezone TS in teradata?

0
votes

I have a requirement in which I have to convert a timestamp {with any timezone} column to CST time zone timestamp.

The incoming timezone will be mentioned in another column, please see the table below:

myTable

I want to calculate '????'?

P.S. My teradata default timezone setting is CST.

I already tried:

select timestamp AT 'America Central'

but it gives me the same output because my default teradata timezone is CST.

I want to be able to give timezone as input with timestamp to teradata and get the output timestamp in CST.

1
timestamptimezoneteradatatimestamp-with-timezone
If you store data without timezone, Teradata stores it as GMT, and automatically converts it to your timezone, which is CST. Isn't that what you want? – Andrew
Not actually, the incoming data can be in any timezone and we need the output timestamp in CST. Also, the column timezone_text would tell us what is timezone of timestamp column {our input}. – Puneet Abichandani
Said another way, there is really no such thing as a timestamp without time zone. The time zone may be expressed as explicit hours/minutes offset from GMT or may be implicit (internal GMT or external session time zone). The AT clause just allows you to obtain the external form relative to a different time zone. – Fred

1 Answers

1
votes

Ideally you would convert the timestamp + string to a TIMESTAMP WITH TIME ZONE type (with numeric hours and minutes offset) as part of your load process. (Store the string separately, in addition, if desired.) For input TIMESTAMP (without time zone), Teradata assumes the input is in the session time zone (of the loading session) and converts to GMT internally. And by default, Teradata implicitly converts from internal format to the session time zone on output.

Teradata does not provide a function to convert from one time zone to another based on separately stored time zone string. You might look at something like Java time for combining separate timestamp and string into a ZonedDateTime. That probably could be incorporated into a UDF to run within Teradata. Or on the client you could use something like Python pytz before loading.