Timestamp with time zone

General
Enthusiast

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 08.43.57.000000 EST’

Output on the sql assistant : ‘7/3/2014 08:43:57.000000’  ---- (suffix EST is missing)

Appreciate your help.

Thanks,

Thanks,

6 REPLIES
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

Enthusiast

Re: Timestamp with time zone

Hi,

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.

thanks.

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'
...
end

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

Enthusiast

Re: Timestamp with time zone

Hi Dieter,

thanks for your help.

I will go ahead with writing UDF.

thanks a lot.

Enthusiast

Re: Timestamp with time zone

Hi 

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

ex-from CET TO PST

Thanks in Adv