Objectives

For nearly 30 years, all of the database applications I manage use UTC as the server and system time zone. While filled with benefits, it adds complexity. With this client’s application, we are expected to book appointments within an Oracle calendar and have them show in the local time zone. And, the “time zone” changes twice per year.

So how do we store dates and times in the database which is in UTC while letting human being schedule appointments without causing users to get confused with local time, Zulu time, UTC, CET, American/New York, -4:00 and +2:00.

Warning: Explicit Language

The trick is to be explicit, always explicit. Dozens of articles and instructions suggest that the developer declare a series of NLS settings in advance. What you assume is the case today will be wrong in the future. For example, you did the prototyping on your desktop version of Oracle, then popped the app to OCI in Northern Virginia, then you port it to AWS in Ireland. But the application is used in central Europe (Belgium, France, Germany).

Make no assumptions and be explicit in every database interaction.

Why UTC

UTC, Universal Time Coordinated, also called “Zulu Time” by several international companies and the U.S. Military is the globally designated coordinated time. All times on the solid earth, floating on the seas, wondering below the seas, or travelling beyond the atmosphere tend to use UTC as the official time.

Study Oracle’s time process closely and you’ll observe that time is not consecutive. 2am on 12MAR2023 in the US was followed by 0301 in several states, counties, and cities, but not all. Some places do not change time. While 0230 12MAR2023 is not permitted in Boston MA, it can be permitted in other city. In the fall of that year, time “fell back” or what ever. Is Boston four hours or is it five hours from UTC? The answer is “Depends”. Go take a look at this article about finding the time-change day in a region.

After several decades working with international crews, I am reminded each spring and fall that nations and regions change time from Winter to Summer Time on different days.

Why UTC? Because it removes the regional strangeness from the daily analysis of our data. It is the same, everywhere for all instances. 0200 is always 0200 UTC. No fingers, no toes needed to determine if that was Brussels, New York, London, LA, or Taiwan.

UTC is absolute time.

Any other time zone is a “relative” time measured from UTC. +1 hours in the winter, +2 hours in the summer (or maybe that is backwards). -4 or -5 depending on the date. Then the offset changes as you change states and even counties in the US. It matters.

APEX Date Page Item

After years of getting fooled by the Oracle APEX Date Page Item, I have done my best to remember that it behaves more like text than a date. Within an Oracle database, a date is a data type and it is distinct from timestamp and timestamp with time zone. Within the client-side environment of APEX, the values of an APEX Date Page Item are stored as date-formatted text. You either explicitly provide the format, or Oracle APEX uses the implicit date format based on current NLS settings.

In this case, TIME_FORMAT looks like this: ‘DD-MON-YYYY HH24.MI‘. Our standard DATE_FORMAT, is 'DD-MON-YYYY‘. Given we are picking the start and end time for appointments, we need to specify the time component (HH24.MI). What you want to do is explicitly use the format mask and be consistent. The most common error when making a mistake with date format mask is an error that reads: “Invalid Month” (ok, typically for US folks working with European dates. Apparently there are not 30 or 31 months in a year – but there are at least 28 days in every calendar month.

The explicit use of date format avoids confusion with both NLS settings and APEX session settings which are both hard to see at-a-glance.

APEX Date Page Items are text fields dressed up to look fancy

Conflict Recognition

Start Date is database field of type timestamp with time zone.

P10_START_DATE is an APEX Date Page Item which is text-like date sort of thing but when APEX does the DML (“data manipulation language”) behind the scenes thinks “Oh this is a date” and it tries to put a date into the database when it does its update.

You can’t necessarily put a normal date field into a database column of type timestamp with time zone. I got an error that stated that the “database version had changed since…” Sure, I’ve seen that message and normally is it spot-on. The DML statement update looks at a hidden (but query-able) version/status of the database row and say: “Someone else was here,” and it will not then overwrite it. It took a few steps and effort to prove that posting a date to a timestamp database field resulted in the same error. If I set the start_date as “query only” or deleted it, the DML was perfect. Add the start_date and it breaks.

In the detective genre, we call this a clue.

Conflict Resolution

Option 1 – convert the database fields to date and ignore the complexity of the timestamp with time zone.

Option 2 – Keep the database fields as timestamp with time zone and help APEX over the hurdle.

Option 1 requires that we work with relative time and we must do some adjustments for time changes and even different regions if we expand to a new time zone.

Option 2 requires hand-coding a solution.

Hand-Coded Solution

The DML (insert, update, delete type statements) involves 2 elements. First we fetch the data from the database. Then we update the data from the database. APEX, when doing its DML does lock the record and notes the version of the row. APEX will prevent data collisions.

First, do your query to fetch the data after loading the page items.

Second, do your update of the fields after the APEX DML. This will cause APEX to release its locks and stop caring about the version of the database.

Query to Fetch Data

Both the Start Date and End Date on the modal page below are timestamps.

The query for the Start and End dates looks like this:

if :P10_TIMESLOT_PK is not null then
  select
    to_char(cast(start_date at time zone 'CET' as date),:TIME_FORMAT) ,
    to_char(cast(end_date at time zone 'CET' as date),:TIME_FORMAT)
  into 
    :P10_START_DATE,
    :P10_END_DATE
  from cm_timeslot
  where timeslot_pk = :P10_TIMESLOT_PK;
end if; 

So the data are stored at UTC. Therefore, we cast them to the correct time zone as a real date then immediately convert that to a text for the APEX Page Item. Note the use of the time format which matches the format needed for the Page Item (see image with “format mask”).

With a little work and some substitutions, you could cast to the APEX session time zone as well. That might be best long term. The trick is to be explicit. Know where you are starting, know your target and know how to hit the target. Trusting the NLS settings or APEX session settings with implicit changes will cause problems or at least inconsistencies when moving code to new servers.

DML to update Timestamp

Create a page-ending process that comes after the APEX Native DML (“process form Corporate Calendar”). In this case, I called it “Update Timestamps”. This code looks like this:

declare
  l_start_timestamp    timestamp with time zone;
  l_end_timestamp      timestamp with time zone;

begin
if :P10_TIMESLOT_PK is not null then
  l_start_timestamp := cm_scheduling_pkg.apex_date_to_timestamp_with_time_zone(
  	P_APEX_DATE	=> :P10_START_DATE,
  	P_FORMAT	=> :TIME_FORMAT
  	);
  l_end_timestamp := cm_scheduling_pkg.apex_date_to_timestamp_with_time_zone(
  	P_APEX_DATE	=> :P10_END_DATE,
  	P_FORMAT	=> :TIME_FORMAT
  	);	
  update cm_timeslot set 
    start_date = l_start_timestamp,
    end_date = l_end_timestamp
    where timeslot_pk = :P10_TIMESLOT_PK;
end if; 
end;

Yes, I wrote a little procedure code in a package. The key issue is the explicit use of the time zones and offsets. Confusingly, the desktop and APEX session may be in 1 time zone with the server and thus database running in UTC.

function apex_date_to_timestamp_with_time_zone(
	P_APEX_DATE	in varchar2,
	P_FORMAT	in varchar2 default 'DD-MON-YYYY HH24.MI'
	) return timestamp with time zone
-- cmoore 27dec2023
-- returns an APEX "date" page item as a timestamp with time zone

as
  l_procedure    varchar2(100 char) 	:= g_package || '.apex_date_to_timestamp_with_time_zone';
  l_timestamp    timestamp with time zone;	
begin
apex_debug.message('begin: ' || l_procedure);

with d as
(
	select 
		 to_char(to_date(P_APEX_DATE,P_FORMAT),P_FORMAT) ate
		,tz_offset(sessiontimezone) offset_sesstime
	from dual
)
select
	to_timestamp_tz(d.ate || ' ' || d.offset_sesstime,'DD-MON-YYYY HH24:MI TZH:TZM')
  into
  l_timestamp
from d;
apex_debug.message('end: ' || l_procedure);
return l_timestamp;
exception when others then
	return null;	
end apex_date_to_timestamp_with_time_zone;

On our team, when we feel like we have explicit and affirmative control over a process, we call it “walking the dog”. As in “Are you walking the dog? Or is it walking you?” Part of the testing involves making deliberate changes to NLS and APEX session states and confirming data by hand. If a meeting say 0900h, then it should show in the database as 0700h, oh, oops, my session is US/Eastern, so minus 6. You get it. You want this to be solid even when the environment changes. So, yes, the information in the following screen shot is accurate!

• Category: APEX
• Tag: apex, oracle, plsql