All Forums General
murugesann 9 posts Joined 07/14
03 Jul 2014
Timestamp with time zone

Hi All,
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 EST’
Output on the sql assistant : ‘7/3/2014 08:43:57.000000’  ---- (suffix EST is missing)
Appreciate your help.

dnoeth 4628 posts Joined 11/04
04 Jul 2014

Hi Murugesh,
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:

  • it will not show 'EST', but '-05:00'
  • 'EST' ist not a valid time zone by default (unless your site added it), should be 'America Eastern' and it might simply be stripped off during conversion


aspiratech 2 posts Joined 07/14
04 Jul 2014

thank for sharing the info

murugesann 9 posts Joined 07/14
04 Jul 2014

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.


dnoeth 4628 posts Joined 11/04
04 Jul 2014

Hi Murigesh,
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 :-)


murugesann 9 posts Joined 07/14
06 Jul 2014

Hi Dieter,
thanks for your help.
I will go ahead with writing UDF.
thanks a lot.


raj786 23 posts Joined 04/14
28 Apr 2015

how to convert one time zone to another tome zone with day light saving in TERADATA.
ex-from CET TO PST
Thanks in Adv

You must sign in to leave a comment.