Error on setting Automatic Timezone in Oracle Apex

Question

I make comparisons of the dates in my data (saved as "due dates" in my data table, as date data type ,without time) with current_date in many pages in my oracle apex application. For example I have one page where I show the "items due for today" in an interactive report by checking the due date of records for their equivalence with current_date, and similarly another page shows the items post due date. The SQL queries generate correct reports but the problem is that current date is calculated as per US (PDT/UCT7) time which is -9 hrs behind from my current region's time, therefore my pages show correct result on afternoon only.

I researched and discovered that setting the application timezone to automatic in application's globalization properties will solve this problem but when I do set the automatic timezone, my application stops working at all and . A "Set time zone" page shows for microsecond but without even letting the user set timezone, page redirects and shows the below error.

Any suggestion to fix this really serious issue will be highly appreciated :(

Answer 1

You mention that your dates are stored in a table of type DATE. Setting "Automatic Timezone" in your application will not change anything. It sets the database session time zone, which is used for columns of datatype "TIMESTAMP WITH LOCAL TIME ZONE". The datatype "DATE" does not know about time zones. To ensure that your users see the same information is every region around the world, you should store your date information in a column of datatype "TIMESTAMP WITH LOCAL TIME ZONE". The "timezone sensitive" equivalent of SYSDATE is CURRENT_TIMESTAMP.

Joel Kallman wrote a blog about this a long time ago, describing this scenario.

https://joelkallman.blogspot.com/2010/09/automatic-time-zone-support-in.html