Timestamp with time zone


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.



Junior Contributor

Re: Timestamp with time zone

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

Re: Timestamp with time zone

thank for sharing the info


Re: Timestamp with time zone


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.


Junior Contributor

Re: Timestamp with time zone

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 :-)


Re: Timestamp with time zone

Hi Dieter,

thanks for your help.

I will go ahead with writing UDF.

thanks a lot.


Re: Timestamp with time zone


how to convert one time zone to another tome zone with day light saving in TERADATA.

ex-from CET TO PST

Thanks in Adv