'How to select datetime from ORACLE db using IST timezone

Oracle database is hosted in US. Application server hosted in UK.

I am trying to access the oracle application from India, how can I write a query that will show IST time. (Local machine time).

This is an oracle form, so no coding can be done.

Tried below but did not worked

SELECT 
TO_CHAR(current_date, 'DD-MON-YYYY HH24:MI')
FROM DUAL
sqloracle


Solution 1:[1]

Indian time is +5.5 hours from GMT, SYS_EXTRACT_UTC(SYSTIMESTAMP) gives time in GMT for any Oracle server

select to_char(SYS_EXTRACT_UTC(SYSTIMESTAMP) + 
       (5.5/24), 'DD-MON-YY HH24:MI:SS') from dual;

Solution 2:[2]

First change the timezone of your session:

ALTER SESSION SET TIME_ZONE = 'Asia/Calcutta';
SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH24:MI') FROM dual;

Note, you cannot use the short name, because 'IST' is also used for "Indian Standard Time" and "Iran Standard Time" and "Israel Standard Time" and "Indonesian Standard Time".

Thus Oracle requires either the full name (see IANA time zone database) or UTC Offset, e.g. ALTER SESSION SET TIME_ZONE = '+5:30';. However, this is more risky in case your country uses DST (Daylight Saving Time)

Solution 3:[3]

Change your timezone with this first,

systimestamp AT TIME ZONE 'IST'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 San
Solution 2
Solution 3 hashbrown

Related Questions