Show timezone associated with timestamp in Oracle

Question

My CRT_TMSTMP field in my Oracle table is a timestamp type field.

I am looking to determine the timezone associated with this field's values as it does not display in my SQL Developer client.

From my research it seems to_char() is not sufficient so I'm wondering what I can do to display this timezone in a query?

Answer 1

A TIMESTAMP doesn't contain any time zone information. The documentation says "It contains fractional seconds but does not have a time zone", but it seems to implicitly be in the same time zone as the database server.

You would need your column to be TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE to store and retrieve a time zone linked to the value.

If you did have a column of one of those types then you could use the TZR date format model element (or TZD, TZH and/or TZM):

select to_char(CRT_TMSTMP, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') from ...

But if you try that with a plain TIMESTAMP it will just report the server time zone. (I thought it errored, but apparently not).

As a demo:

create table t42 (CRT_TMSTMP timestamp,
  CRT_TMSTMP_TZ timestamp with time zone,
  CRT_TMSTMP_LTZ timestamp with local time zone);

insert into t42 values (current_timestamp,
  current_timestamp at time zone 'AMERICA/NEW_YORK',
  current_timestamp at time zone 'AMERICA/LOS_ANGELES');

select to_char(CRT_TMSTMP, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as ts,
  to_char(CRT_TMSTMP_TZ, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as ts_tz,
  to_char(CRT_TMSTMP_LTZ, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as ts_ltz
from t42;

TS                                                             TS_TZ                                                          TS_LTZ                                                       
-------------------------------------------------------------- -------------------------------------------------------------- --------------------------------------------------------------
2014-07-17 17:09:36.673 +00:00                                 2014-07-17 12:09:36.673 AMERICA/NEW_YORK                       2014-07-17 17:09:36.673 EUROPE/LONDON                          

SQL Fiddle.