How to show dates in a given time zone when the value is stored in a DATE data type

Firstly, this post has nothing to do specifically with APEX.  It has everything to do with date conversions in Oracle SQL and how to convert them to a different time zone.  This emanated from a request from our Senior Vice President Michael Hichwa, who asked exactly this question:

"How can I show dates in a given timezone when the date value is stored in a DATE datavtype?  I would like display a calendar in my time zone not GMT.  I don’t want to change my default data type in my table and the CREATED column to be a time stamp with local time zone; I want to keep it as DATE."

I typed out a reply in email tonight, and I thought I would share this here, as I imagine this answer has general applicability.

Two things are necessary to make this happen.

1. Firstly, you must know the time zone of the server where you're running at.  On a system like apex.oracle.com, it's UTC, but this can be confirmed via to_char(systimestamp, 'TZR').  This is important, because this would tell you the correct implicit time zone for your system-generated DATE values like SYSDATE.  If your program hasn't done any other ALTERs of the time zone in your session, then this should be equal to SESSIONTIMEZONE.
2. Once you have the base time zone (which you know is the implicit time zone of all of your system-generated data values), you can now easily convert this to another time zone via AT TIME ZONE.

The syntax is:

from_tz(cast({date_value} as timestamp), to_char(systimestamp,'TZR')) at time zone {preferred_time_zone}

What this says:

• Convert your date to a timestamp using CAST
• Use FROM_TZ to convert it to a timestamp with time zone, and specify the time zone region of the server
• Use AT TIME ZONE to convert it to your preferred time zone

Here's a good example on apex.oracle.com, to take SYSDATE and convert it to my US East Coast time zone:

select from_tz( cast( SYSDATE as timestamp ),
                to_char(systimestamp, 'TZR')) at time zone 'US/Eastern',
       to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')
  from dual

I executed this just now and it gave me the results:

07-MAR-20 09.06.45.000000 PM US/EASTERN

03/08/2020 02:06:45

which makes perfect sense.  The SYSDATE is already tomorrow (Sunday, March 8), but converting it to my local time zone, I got 9:06 PM on March 7, which was exactly the time of day in Ohio when I was testing this.

If I were designing a new application, I would most often take advantage of the time zone-aware data types like TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.  But most people store dates as type DATE, and this is an easy way to convert it to another time zone.