Display Time Zone Description In To_char() In Oracle

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 as 2015-08-06, 04:09:10 +05:30 Any

Solution 1:

In general you cannot show time zone of TIMESTAMP WITH LOCAL TIME ZONE value, because it is always your current local time zone by definition.

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

SELECT SESSIONTIMEZONE FROM dual;

You cannot do

ALTER SESSION SET TIME_ZONE ='IST';

Because IST is also used for "Iceland Standard Time", "Ireland Standard Time", "Israel Standard Time", etc.

However, you can use this:

SELECT TO_CHAR(CAST(LOCALTIMESTAMPASTIMESTAMPWITHLOCALTIME ZONE), 'YYYY-MM-DD, HH24:MI:SS TZD') FROM dual;

Note, TZD means "Daylight savings information". In case India has Daylight saving your TZD may change.

Solution 2:

I have converted the TIMESTAMP To UTC and added 5.5 hrs to it. Like below

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

Hope this works for you.