Creating a mobile app with APEX - Part 7: Working with Time items

In part 5 of the Mobile App series the Form page was refined.
On this page there are two items containing a time value. This post will cover how to deal with these time values.

Time values are stored in the database as time fraction of a Date column. The time values in TTM are stored in the column act_start_date and act_end_date.

Three aspects will be discussed:

• retrieving time values
• processing time input
• entering time values
• special Time Input Control

Retrieving Time values

Oracle APEX does not have an item to display time values, like the Date picker does for date values. 

So we will have to display the formatted time in a Text Item. 

And the time values are not available directly in the table. So we will need a view to expose the time values from the Date columns:

create or replace force view ttm_activities_vw  as 

select act.act_id

     , trunc(act_start_datetime)               as  act_start_date

     , act.act_prj_id

     , prj.prj_name                            as  act_prj_name

     , ttm_alg.date2time(act_start_datetime)   as  act_start_time

     , ttm_alg.date2time(act_end_datetime)     as  act_end_time

     , act_description

     , act_location

from   ttm_activities     act

  join ttm_projects       prj

       on  prj.prj_id = act.act_prj_id;

This view (which is already created) exposes the start and end time in the format hh24:mi.
You can find the source of the package ttm_alg here.

Processing time input

The view of the last paragraph is the base table for the APEX form page.
In order for this to function an Instead of trigger needs to be defined, performing the insert/update/delete action. Below the code of the trigger:

create or replace trigger act_io

  instead of insert or update or delete

  on ttm_activities_vw

  for each row

begin

  if inserting then

    insert into ttm_activities

         ( act_prj_id

         , act_start_datetime

         , act_end_datetime

         , act_description

         , act_location

         )

    values

         ( :new.act_prj_id

         , ttm_alg.time2date(:new.act_start_date,:new.act_start_time)

         , ttm_alg.time2date(:new.act_start_date,:new.act_end_time)

         , :new.act_description

         , :new.act_location

         );

  elsif updating then

    update ttm_activities

    set    act_prj_id = :new.act_prj_id

         , act_start_datetime = 

                   ttm_alg.time2date(:new.act_start_date,:new.act_start_time) 

         , act_end_datetime   = 

                   ttm_alg.time2date(:new.act_start_date,:new.act_end_time)

         , act_description    = :new.act_description

         , act_location       = :new.act_location

    where  act_id = :new.act_id

    ;    

  elsif deleting then

    delete ttm_activities

    where  act_id = :old.act_id

    ;

  end if;

end;

As you see the code is much of a one-on-one conversion from view to table except for the time values. The bold code converts the time values into dates with time fraction. 

With this trigger insert, update and delete can be performed on the view and therefore the view can be source of an APEX form. So all logic is performed in the database. 

Entering time values

The time values are displayed in APEX using a Text Item.

The time values can be entered using the format hh24:mi for example 9:15. 

There is no need to add preceding zeros, so 9:05 can be entered as 9:5. 

Also for the hours the minutes can be totally omitted, so 9:00 can be entered as 9.

Time values can also be entered with touch gestures using the Touch Time Input control. 

Special Time Input control

Back in 2009, when I had my first Android phone, I wanted to do my time registration using the phone. Thinking about this I got the idea for a graphical time input control where a clock image is displayed on the phone and the user would use the touch screen to draw the hands on the clock, thus entering the time.

It would take some time before I would create this control. In february 2015 I wrote a blogpost about my Time Input Control, and in 2017 I published the Touch Time Input APEX plug-in on apex.world:

Recently I published a new version of the plug-in with support for floating labels. It is also easier to implement as the time picker icon is created by the plugin. The changes are described in this blogpost.

We will add a time picker buttons to the time items and connect the Time Input DA to these buttons:

• Open Page 15
• Goto the tab Dynamic Actions
• Right click on Page Load and chose Create Dynamic Action
• Select the new dynamic action and set the Name to Page Load
• Select the action and set Identification > Action  to Touch Time Input V2 [Plug-In]

This will take care that all items with the class has-time-picker will be added with a time picker icon. Now we will mark the time items:

• On Page 15 open the region Activity
• Select the item P15_ACT_START_TIME
• Set the value of Advanced > CSS classes to has-time-picker
• Repeat for item P15_ACT_END_TIME

That's it!