Display timezone description in to_char () in Oracle

Question

I have a SQL query

select to_char(cast(sysdate as timestamp with LOCAL time zone), 'YYYY-MM-DD, HH24:MI:SS TZR') from dual 

This return output is like

 2015-08-06, 04:09:10 +05:30 

Does anyone have a key to get the output as follows

 2015-08-06, 04:09:10 IST 

Thanks in advance.

Answer 1

In general, you cannot display the time zone of the TIMESTAMP WITH LOCAL TIME ZONE value, since by default this is your current local time zone.

+05:30 is your current time zone, you can confirm this:

 SELECT SESSIONTIMEZONE FROM dual; 

You cannot do

 ALTER SESSION SET TIME_ZONE = 'IST'; 

Because IST also used for "Icelandic Standard Time", "Irish Standard Time", "Israeli Standard Time", etc.

However, you can use this:

 SELECT TO_CHAR(CAST(LOCALTIMESTAMP AS TIMESTAMP WITH LOCAL TIME ZONE), 'YYYY-MM-DD, HH24:MI:SS TZD') FROM dual; 

Note. TZD stands for TZD Information. In the event that I have daylight saving time, your TZD may change.

Answer 2

I converted TIMESTAMP to UTC and added 5.5 hours to it. As below

 SELECT TO_CHAR(SYS_EXTRACT_UTC(systimestamp) + (5.5/24))|| ' IST' FROM DUAL; 

Hope this works for you.