We are trying to load timestamp with time zone ( say EST or IST) into ‘timestamp(6) with time zone’ column. when i try to load the load with the ‘EST’ suffix it is working fine but the thing is while retrieving it is not showing with the time zone as ‘EST’ in sql assistant . Is there any other way to see the data?
Table structure : timestamp_tbl (timestamp_col TIMESTAMP(6) WITH TIME ZONE)
Input data : ‘2014-07-03 08.43.57.000000 EST’
Output on the sql assistant : ‘7/3/2014 08:43:57.000000’ ---- (suffix EST is missing)
Appreciate your help.
you probably use an ODBC connection.
There are some known limitations in ODBC regarding TIME, "Time fields do not display fractional seconds or Time Zone information.", probably similar for TIMESTAMPs.
When you switch to .Net you'll get the time zone, but:
thanks for the information.
I switched to .NET connection.
Now I can see the records with suffix +04:00
But i have this requirement. Time zone region('EST' or 'America Eastern') should be displayed instead of +04:00.
Is there any way to do that?
I found that we can set the time zone string formats in Teradata Locale Definition Utility (tdlocaledef).
Can I achieve the solution for above mentioned Requirement with this info.
afaik you can't do that.
The time zones in tdlocaledef are only used for casting strings to a timestamp and not vice versa. There's a one-to-many relation between time zone names and UTC, e.g. -05:00 might be Eastern Standard or Central Daylight Time, according to Wikipedia -05:00 indicates seven time zone names.
Btw, -04:00 should be Eastern Daylight Time, EDT.
So the only solutionis to write it on your own like
cast(ts as char(26)) ||
case extract(timezone_hour from ts)
when -4 then ' EDT'
when -5 then ' EST'
You could wrap that in a SQL UDF, but this will get really complicated with daylight saving time zones. So you might better go for a C-UDF or keep -04:00 :-)