Seek where you can help. Now.

I have a simple request of the global Oracle APEX Community, please:

Seek where you can help.  Now.

These are interesting times we're living in, unlike anything I've witnessed in my lifetime. And people are rapidly understanding that they have many information needs, which they needed fulfilled yesterday.  I said this earlier about Brexit and I see the same thing unfolding for COVID-19.  People need information management applications that they've never even dreamed of, but they need them immediately.  This is a classic case of opportunistic applications.  And who better to understand how to design and deliver applications in hours or days than the global Oracle APEX community?  That's you!

If you're on social media, you may have seen Oracle APEX used in a number of interesting scenarios.  Here are a few I'm aware of:

• In China, Buling Zhang the director of IT at a hospital, wrote an APEX app to gather information about patients (travel history, symptoms, etc.).  He subsequently shared this app with 6 other hospitals.
• In United Arab Emirates, Muhammad Rehan Javed wrote an APEX application to track attendance.  It was developed in 2 hours and rolled out to 10,000 users.  As he said, it "facilitated in avoiding contact with biometric devices".
• In the United Kingdom, Dave Waghorn and his team at University Hospital Southampton developed APEX apps in 3 days to help support the handling of COVID-19.

I'm sure you're aware of many more.  I encourage you to find where you can help...not tomorrow, but today.

We have offered our team's services to a couple large organizations, and I strongly encourage you to do the same.  Be proactive about it.  Don't wait for someone to approach you.  Contact your ministry of public health or clinic or hospital at the national, state or local level.  Tell them what you can do and help them manage the information needs they're being overrun with.  Better yet, show them what you can do.  You are talented and experienced to help solve problems exactly like these.

This isn't about Oracle or APEX or even the community.  I'd be satisfied if they never knew what you used to provide a solution for them.  This is about helping people who desperately need your help and expertise, and they are currently unaware of you and what you can deliver.  Please contact someone today.

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.