Setting Timezone in Oracle APEX

But it returns time in wrong timezone. What did I miss?

I cringed a little when reading this. You missed how time zones work in your database. This means you may have a lot of time zone issues baked into your data model now. I hope I'm wrong! :)

Rather than try to fix this one specific problem, you should start by learning about how Oracle works with temporal values.

1. Start with the official doc here: https://docs.oracle.com/en/database/oracle/oracle-database/18/nlspg/datetime-data-types-and-time-zone-support.html#GUID-7A1BA319-767A-43CC-A579-4DAC7063B243
2. I made an attempt at distilling some of that for Node.js (and APEX) developers here: https://jsao.io/2016/08/working-with-dates-in-javascript-json-and-oracle-database/ You might want to read that first.
3. Also, read these related blog posts from Joel Kallman: https://joelkallman.blogspot.com/2010/09/automatic-time-zone-support-in.html and https://joelkallman.blogspot.com/2020/03/how-to-show-dates-in-given-time-zone.html.

You'll know you're ready to tackle the problem when you can answer these questions:

1. What is the database time zone and what is it used for?
2. What is the session time zone and what is it used for?
3. How do the following functions work with time zones: SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP?
4. How can the session time zone be set in APEX?

Solution 1:

BEGIN
    APEX_UTIL.SET_SESSION_TIME_ZONE(P_TIME_ZONE => '<you_timezone_here>');
END;

Solution 2:

BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET TIME_ZONE = ''<you_time_zone_here>''';
END;

For all database sessions, both solutions can be implemented in Edit Application Definition -> Security -> Database Session -> Initialization PL/SQL Code. For especific code, add the chosen solution into PL/SQL code.