Hi,
Looking to display a set of dates from a table (data type is date) to include the time zone from the DB.
This is what I have so far:
ORT2 SQL> select to_char( cast(sysdate as timestamp with local time zone), 'Dy Mon dd hh24:mi:ss TZR yyyy') from dual;
TO_CHAR(CAST(SYSDATEASTIMESTAMPWITHLOCALTIMEZONE),'DYMOND
---------------------------------------------------------
Thu Jan 22 02:58:05 -08:00 2015
But I need to show it with the time zone abbreviation instead of the time offset itself, so I used this:
ORT2 SQL> select to_char( cast(sysdate as timestamp with local time zone), 'Dy Mon dd hh24:mi:ss TZD yyyy') from dual;
TO_CHAR(CAST(SYSDATEASTIMESTAMP
-------------------------------
Thu Jan 22 02:59:07 2015
..the abbreviation is missing. Session time zone is set:
ORT2 SQL> SELECT SESSIONTIMEZONE from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
-08:00
..however when I change it to something else my select works:
ORT2 SQL> alter session set time_zone='Canada/Mountain';
Session altered.
ORT2 SQL> SELECT SESSIONTIMEZONE from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
Canada/Mountain
ORT2 SQL> select to_char( cast(sysdate as timestamp with local time zone), 'Dy Mon dd hh24:mi:ss TZD yyyy') from dual;
TO_CHAR(CAST(SYSDATEASTIMESTAMP
-------------------------------
Thu Jan 22 03:00:29 MST 2015
Is there another way to get the timezone abbreviation to show? Or do I need to set the sessiontimezone before I can use this method?
Thanks!