Objectives

My team and I need to book an appointment on a timeslot. For us mortals, the job is easy, we look at a calendar click an open spot and add an appointment with a wizard. Using Oracle pl/sql and database technology, the entire structure has to be built so that an automated hand can pick an open spot and add an appointment.

Step 1 of this task is to build a calendar frame work using APEX and PL/SQL that builds out timeslots that can be marked at available, not available, etc.

Step 2 and more will be in follow on articles. In the next article, we will add a layer where we can book or plan an appointment on the timeslot calendar.

Putting an Appointment on an Oracle APEX Calendar is the follow on article in this series.

Target

This process will build a number of calendars. At the foundation is a corporate calendar that will be used to define holidays and no-go timeslots. In my real-world examples, this corporate calendar provide additional power. We’ll stay focused on the go/no-go process. We did color code them green for “go” and black for “no-go”.

We will then build calendars for each employee. Again, these will be color and data coded for available, appointment, and not-available (green, red, black).

With querying, we can prevent employees from booking “available” on top of corporate “unavailable” timeslots.

Challenges

During 2023/2024, I wrote two articles on working with timestamp with local time zone. While I advocate for setting Oracle database servers to run on UTC, we mortals struggle leaping back and forth between UTC and our own lovely slice of time. In fact, if you can picture that “slice of time” it would look like a section of a peeled orange. Picture an orange with 24 slices. Each slice would be occupy 15 degree of an arc (thanks Math). In the ideal world, each time zone is a 15 degree arc of land and ocean on the earth. We messed with that a bit and made neat lines jagged and such. Blame that on geopolitics and not math.
We need to present information to users who are booking appointments in their current time. This involves casting and working with timestamp with local time zone.

Diversion on 15 degrees

Just because I grew up sailing, I’ll share a trick I learned when a kid on telling time with my hand. As just established the sun moved through 15 degrees of the sky each hour of the day. When the sun is 15 degrees off the horizon, you have 1 hour before it makes that hissing sound and sinks into the ocean again (or is that Apollo and his chariot? Or Ra? You get the point. Two fingers is 30 minutes. 4 hand-widths is 4 hours. With this trick you can always estimate the time from sunrise or the time to sunset with your hand.

Place your hand sideway with the pinkie at the top and forefinger down. Stretch your arm at full length. Place the bottom of your hand at the horizon and measure hand widths.

We can call this an analog clock! A little easier than building the framework for a robust corporate calendar system for appointments with Oracle pl/sql.

Back to Calendars with Oracle

My approach to building a calendar with appointments for an Oracle database is to separate the two concepts of a calendar and appointments. The advantage that digital calendars have is that they built the framework of days, dates, and such. For example, Microsoft Outlook and Google Calendars have this sort of familiar structure:

For us, we need to build this as a framework or foundation.

Calendar Framework

Within an Oracle database, we can represent a timeslot with start date and end date (yes, I’ll use “date” where you might expect the word “time” because in this world, they are the same). From my December 2023 article, you’ll see an exploration of timestamp with time zone as a data field type. The reminder is that Oracle APEX treats page items as varchar2 fields. While Oracle drives home the “low code” nature of APEX, you can use APEX for robust enterprise level application development. You can go “high code” within Oracle and win huge benefits. This December 2023 article demonstrates how to use timestamp with time zone within an APEX page. This resolves the issue of managing a server in UTC and displaying data that adjusts nicely for the user’s time zone.

At the bottom of this article is a table definition for Timeslots within our planning/calendar module. This table serves two functions.

Employee Foreign Key is null

When Employee Foreign Key is null, then this entry represents the corporate calendar. We can store:

• Corporate holidays (no appointment allowed/not available)
• Corporate functions
• Workhours

With a query, you can rapidly know if you are trying to schedule an appointment on during a timeslot that corporate already blocked out as a no-go timeslot.

Ok, yes, the production system has a few features not in the article such as permitting multiple appointments for clients to self-book when purchasing services through eCommerce. We can say 10 appointments are available on 29APR. This permits self-service but within limits. That’s for another chat.

Employee Foreign Key Populated

With a row linked to an employee, we can then setup the employee’s calendar. The timeslot types we expect to see for employees are:

• Available
• Unavailable
• Inspection/Booked/Appointment
• Personal

And a few others that the client asked for. Unavailable is a black block in the calendar. It is a time that can never be booked automatically. Where as a timeslot that is marked as “Appointment” or “Inspection” can be cancelled, moved, &c. If an Appointment is cancelled, then the time reverts to “Available”.

The decision we made is that we can only book appointments on time slots that are Available. Why? Because when you query data it is significantly easier to hit a target and get feedback that you hit a target, then it is to hit an null set of data. Nobody wants an appointment at 2am on Sunday morning. I mean it happens, but please don’t build a system that can routinely book appointments from a database that decides to wake me up!

Therefore we treat the null set of data as not available. The rule is Oracle can put an appointment on the green, but not on the blank and not on the black.

Query to find

select
  ts.timeslot_pk,
  ts.employee_fk,
  e.first_name || ' ' || e.last_name inspector,
  ts.start_date,
  ts.timeslot_type_fk,
  ts.end_date
from pln_timeslot ts
left join emp_employee e on e.employee_pk = ts.employee_fk
where ts.timeslot_type_fk = 'AVAILABLE'
and cast(to_date('2024-04-12T1000','YYYY-MM-DD"T"HHMI')as timestamp) at time zone 'CET' 
  between ts.start_date and ts.end_date;

You can see that we have 3 employees available during normal work hours on 12APR2024.

There is work required by users to affirmative populate their workhours. We build a cool scheduler that permits doing months at a time within patterns to relieve some of the burden.

Code that returns if a timeslot is available

function timeslot_available(
  P_EMPLOYEE_PK        in emp_employee.employee_pk%type,
  P_START_DATE        in date,
  P_END_DATE          in date
  ) return varchar2
-- cmoore 27dec2023
-- tests if a range of time is available
-- if available, it returns a colon-separate string of timeslot_PK
-- if not available, it returns null
-- the call

/*
begin  
dbms_output.put_line(pln_scheduling_pkg.timeslot_available(
  P_EMPLOYEE_PK        => 747,
  P_START_DATE        => to_date('12-JAN-2024 08.00','DD-MON-YYYY HH24.MI'),
  P_END_DATE          => to_date('12-JAN-2024 12.00','DD-MON-YYYY HH24.MI')
  ));
end;
*/
as
  l_procedure         varchar2(100 char)     := g_package || '.timeslot_available';
  l_select_count      number;
  l_interval          number                := 15; -- minutes
  l_minutes_per_day   number                := 24 * 60;
  l_skip              number;
  l_date_counter      date;
  l_timeslot_pk       pln_timeslot.timeslot_pk%type;
  l_last_pk           pln_timeslot.timeslot_pk%type;
  l_timeslot_pks      varchar2(1000 char);
begin
apex_debug.message('begin: ' || l_procedure);
-- validate parameters
if P_EMPLOYEE_PK is not null then
  emp_employee_pkg.validate_pk(P_EMPLOYEE_PK,l_procedure);
end if;
if P_END_DATE <= P_START_DATE then
  raise_application_error(-20001,'End Date is before Start Date in ' || l_procedure);
end if;
l_skip            := l_interval / l_minutes_per_day;
l_date_counter    := P_START_DATE;
apex_debug.message('P_START_DATE: ' || to_char(P_START_DATE,'DD-MON-YYYY HH24.MI'));

while l_date_counter < P_END_DATE loop
  select count(1) into l_select_count
    from pln_timeslot
      where l_date_counter between cast(start_date at time zone 'CET' as date) 
                                  and cast(end_date at time zone 'CET' as date)
      and (
        employee_fk is null
        or
        employee_fk = nvl(P_EMPLOYEE_PK,-1)
        );
  if l_select_count > 0 then -- timeslot is available
    select timeslot_pk into l_timeslot_pk
      from pln_timeslot
      where l_date_counter between cast(start_date at time zone 'CET' as date) 
                                  and cast(end_date at time zone 'CET' as date)
      and (
        employee_fk is null
        or
        employee_fk = nvl(P_EMPLOYEE_PK, -1)
        )      
      fetch first row only;
    
  end if; -- available found
  if l_timeslot_pks is null and l_timeslot_pk is not null then
    l_timeslot_pks     := l_timeslot_pk;
  else
    if nvl(l_last_pk,-1) <> l_timeslot_pk then
      l_timeslot_pks   := l_timeslot_pks || ':' || trim(to_char(l_timeslot_pk));
    end if; -- don't add same PK over and over
  end if;
  apex_debug.message('l_date_counter: ' || to_char(l_date_counter,'DD-MON-YYYY HH24.MI'));  
  apex_debug.message('l_timeslot_pk: ' || l_timeslot_pk);
  apex_debug.message('l_timeslot_pks: ' || l_timeslot_pks);  
  -- reset/set values for next loop
  l_last_pk           := l_timeslot_pk;
  l_timeslot_pk        := null;
  l_date_counter      := l_date_counter + l_skip; -- add 15 minutes

end loop; -- l_date_counter <= P_END_DATE
apex_debug.message('end: ' || l_procedure);
return l_timeslot_pks;
end timeslot_available;

Traps and Challenges

The idea that railroads triggered the standardization of time so that every village didn’t have a different 10 am is cool story. So is the story of using time for calculating longitude. Then we (the globe) fought over which meridian line represents 0. It boiled down to England (Greenwich) or France (Paris).

Know what we didn’t standardize?

How we count the days in the week! Some nations claim Sunday is day 1 and others claim Monday is day 1 in each week. Oracle tries to keep up with “WW” and “IW”. Even after testing, we still had to fuss at this code to get Tuesdays to land on, well, a Tuesday.

--WW is week number, IW is ISO week
select
  to_number(to_char(monday,'IW'))  week_number,
  monday        mon,
  monday + 1    tue,
  monday + 2    wed,
  monday + 3    thu,
  monday + 4    fri,
  monday + 5    sat,
  monday + 6    sun,
  start_date
  from (
  with i as 
  (
    select
    z.start_date                              start_date,
    to_number(to_char(z.start_date,'IW'))     week_number,
    to_number(to_char(z.start_date,'D'))      day_number,
    z.start_date - to_number(to_char(z.start_date,'D') - 1) first_day_week0
    from (
    select
      P_START_DATE         start_date
    from dual
      ) z  
  )
  select
    i.first_day_week0 + ((rownum - 1) * 7) monday,
    i.start_date
  from i
  connect by rownum <= P_NUMBER_OF_WEEKS 
)

Tables & Definitions

These tables are functional but pulled from a production system we are developing. I’ve done a bit to nibble out possible links to tables not defined and such. For purposes of this article, the Timeslot Types in play are: AVAILABLE and INSPECTION (which is not available, you can substitute “appointment” for that term if you’d like).

Planning Timeslots for Calendar

CREATE TABLE PLN_TIMESLOT (
  TIMESLOT_PK 			NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 3105 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	TIMESLOT_FK 		NUMBER, 
	BEFORE_TIMESLOT_FK 	NUMBER, 
	AFTER_TIMESLOT_FK 	NUMBER, 
	EMPLOYEE_FK 		NUMBER, 
	TIMESLOT_TYPE_FK 	VARCHAR2(50 CHAR) NOT NULL ENABLE, 
	TITLE 			VARCHAR2(400 CHAR), 
	DESCRIPTION 		VARCHAR2(4000 CHAR)
	SLOTS_AVAILABLE 	NUMBER, 
	START_DATE 		TIMESTAMP (6) WITH LOCAL TIME ZONE, 
	END_DATE 		TIMESTAMP (6) WITH LOCAL TIME ZONE, 
	DURATION_MINUTES 	NUMBER, 
	CREATED_DATE 		TIMESTAMP (6) WITH LOCAL TIME ZONE NOT NULL ENABLE, 
	CREATED_BY 		VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	UPDATED_DATE 		TIMESTAMP (6) WITH LOCAL TIME ZONE NOT NULL ENABLE, 
	UPDATED_BY 		VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	 CONSTRAINT PLN_TIMESLOT_PK PRIMARY KEY (TIMESLOT_PK)
  USING INDEX  ENABLE, 
	 CONSTRAINT PLN_TIMESLOT_FK01 FOREIGN KEY (TIMESLOT_FK)
	  REFERENCES PLN_TIMESLOT (TIMESLOT_PK) ENABLE, 
	 CONSTRAINT PLN_TIMESLOT_FK02 FOREIGN KEY (BEFORE_TIMESLOT_FK)
	  REFERENCES PLN_TIMESLOT (TIMESLOT_PK) ENABLE, 
	 CONSTRAINT PLN_TIMESLOT_FK03 FOREIGN KEY (AFTER_TIMESLOT_FK)
	  REFERENCES PLN_TIMESLOT (TIMESLOT_PK) ENABLE, 
--	 CONSTRAINT PLN_TIMESLOT_FK04 FOREIGN KEY (EMPLOYEE_FK)
--	  REFERENCES EMP_EMPLOYEE (EMPLOYEE_PK) ENABLE, 
--	 CONSTRAINT PLN_TIMESLOT_FK05 FOREIGN KEY (TIMESLOT_TYPE_FK)
--	  REFERENCES LU_TIMESLOT_TYPE (TIMESLOT_TYPE_PK) ENABLE
   ) ;

COMMENT ON TABLE PLN_TIMESLOT  IS 'Calendar for scheduling. 1 row per employee, plus 1 for corporate';
CREATE INDEX PLN_TIMESLOT_I01 ON PLN_TIMESLOT (TIMESLOT_FK);
CREATE INDEX PLN_TIMESLOT_I02 ON PLN_TIMESLOT (EMPLOYEE_FK); 
CREATE INDEX PLN_TIMESLOT_I03 ON PLN_TIMESLOT (TIMESLOT_TYPE_FK); 
CREATE INDEX PLN_TIMESLOT_I04 ON PLN_TIMESLOT (START_DATE); 
CREATE INDEX PLN_TIMESLOT_I05 ON PLN_TIMESLOT (END_DATE); 

CREATE OR REPLACE EDITIONABLE TRIGGER PLN_TIMESLOT_BIU 
before insert or update on pln_timeslot
for each row
begin
	if inserting then
		:new.created_date := localtimestamp;
		:new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
	end if;
	:new.updated_date := localtimestamp;
	:new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end pln_timeslot_biu;

/
ALTER TRIGGER PLN_TIMESLOT_BIU ENABLE;

• Category: APEX, plsql
• Tag: apex, oracle, Oracle Standard Time, sql