Hanumanth Reddy Hanumanth Reddy - 19 days ago 11
SQL Question

How to get country based time zone in date format in oracle?

I am trying the following query to get the orders and order date as "Mon Jan 01 12:55:55 2016 IST" format, where IST is Indian Standard Time, but I am able to get "Mon Jan 01 12:55:55 2016".

I don't know how to get the time zone. I mean which parameter has to be passed to get it. Could you please help me in resolving this.

SELECT ORDERID AS ORD_ID, TO_CHAR(ORDERDATE, 'Dy Mon dd HH24:MI:SS yyyy') AS ORDER_DATE
FROM ORDER


Thanks
Hanumanth Reddy.

Answer

It depends on the data type of column ORDERDATE. Data types DATE or TIMESTAMP do not contain any time zone information. In such case you can append IST only as static text, for example like this.

SELECT ORDERID AS ORD_ID, 
   TO_CHAR(ORDERDATE, 'Dy Mon dd HH24:MI:SS yyyy')||' IST' AS ORDER_DATE
FROM ORDER;
SELECT ORDERID AS ORD_ID, 
   TO_CHAR(ORDERDATE, 'Dy Mon dd HH24:MI:SS yyyy "IST"') AS ORDER_DATE
FROM ORDER;

In case data type of ORDERDATE is TIMESTAMP you can also attach your time zone and use this for output:

SELECT ORDERID AS ORD_ID, 
   TO_CHAR(ORDERDATE AT TIME ZONE 'Asia/Calcutta', 'Dy Mon dd HH24:MI:SS yyyy TZD') AS ORDER_DATE
FROM ORDER;
SELECT ORDERID AS ORD_ID, 
   TO_CHAR(FROM_TZ(ORDERDATE , 'Asia/Calcutta'), 'Dy Mon dd HH24:MI:SS yyyy TZD') AS ORDER_DATE
FROM ORDER;
ALTER SESSION SET TIME ZONE = 'Asia/Calcutta';
SELECT ORDERID AS ORD_ID, 
   TO_CHAR(ORDERDATE AT LOCAL, 'Dy Mon dd HH24:MI:SS yyyy TZD') AS ORDER_DATE
FROM ORDER;

In case data type of ORDERDATE is TIMESTAMP WITH TIME ZONE you can simply do:

ALTER SESSION SET TIME ZONE = 'Asia/Calcutta';
SELECT ORDERID AS ORD_ID, 
   TO_CHAR(ORDERDATE, 'Dy Mon dd HH24:MI:SS yyyy TZD') AS ORDER_DATE
FROM ORDER;

Note, apart from the last query all of these will work properly only if values in ORDERDATE are stored as 'Asia/Calcutta' time.

Another note, you cannot make ALTER SESSION SET TIME ZONE = 'IST'; or FROM_TZ(ORDERDATE, 'IST'). IST could also mean "Israel Standard Time" or "Iceland Standard Time" (and maybe even more), thus you will get an error in Oracle. Time zone name Asia/Kolkata is also fine, provided you have a recent time-zone file installed at your database.