Working with INTERVALs in your Oracle REST APIs

I’ve experienced a new love affair with the INTERVAL data type. I have been tracking my runs, bike rides, etc. with my Garmin watch and cross-logging those to the Strava app for several years now.

It makes for fun data to play with. And let’s be honest, I do too much with the BEER (Untappd) data.

An activity is stored with its duration and distance, both as INTEGERs. You can ‘guess’ that those are treated as SECONDS and METERS, respectively.

Here’s a sneak at my table and how it’s defined –

CREATE TABLE "ACTIVITIES" 
   (	"ID" NUMBER(*,0), 
	"ACTIVITY_DATE" DATE, 
	"ACTIVITY_NAME" VARCHAR2(100), 
	"ACTIVITY_TYPE" VARCHAR2(100), 
	"DESCRIPTION" VARCHAR2(4000), 
	"ELAPSED_TIME" NUMBER(*,0), 
	"DISTANCE" NUMBER(*,0), 
	"COMMUTE" VARCHAR2(10)", 
	"GEAR" VARCHAR2(100)", 
	"FILENAME" VARCHAR2(100)"
   ) ;

So, when I query that stuff back out, I make a couple of function calls to make the data easier to read for us HUMANS.

The interesting bits here are the last column in the SELECT:

NUMTODSINTERVAL(ELAPSED_TIME, 'second') "TIME"

I’m using a function to convert the number to an INTERVAL, and I’m telling the function that the incoming number is expressed as SECONDs.

When the data comes out, I can see, “+0 8:0:0” – which is 8 hours, 0 minutes, 0 seconds. The + indicates a positive value, and the leading 0 is the number of days.

Ok, so let’s tun this query into a REST API.

How ORDS Treats INTERVALs

If I take that same query and map it to a GET handler, and call it, I’ll get back my JSON results, and the INTERVALs will appear as so –

Or, if you look at the ISO standards for working with a duration of time,

PT – P is the duration designator (for period), and T is the time designator.

After that we get 7H – 7 hours, 3M – 3 minutes, and 33S – 33 seconds.

So that’s one way to we can work with INTERVALs, simply querying them. But what if I want to POST or PUT some INTERVAL data up to my tables?

Some TABLE CRUD via REST APIs for Intervals

So I’m going to create a table, and POST some data to it.

Our TABLE comes courtesy of Tim, again. Here’s a script we can run to get started with a couple of rows.

drop table test_interval_table;

CREATE TABLE test_interval_table (
  id             NUMBER(10),
  time_period_1  INTERVAL YEAR TO MONTH,
  time_period_2  INTERVAL DAY TO SECOND,
  time_period_3  INTERVAL YEAR (3) TO MONTH,
  time_period_4  INTERVAL DAY (4) TO SECOND (9)
);

insert into test_interval_table (id,
                                 time_period_1,
                                 time_period_2,
                                 time_period_3,
                                 time_period_4)
 VALUES (
          1,
          INTERVAL '9-6' YEAR TO MONTH,
          INTERVAL '2 11:59:59' DAY TO SECOND,
          INTERVAL '1-1' YEAR TO MONTH,
          INTERVAL '2 01:00:00.333' DAY TO SECOND(3)
);

insert into test_interval_table (id,
                                 time_period_1,
                                 time_period_2,
                                 time_period_3,
                                 time_period_4)
 VALUES (
          2,
          INTERVAL '21-2' YEAR TO MONTH,
          INTERVAL '2 3:04:11' DAY TO SECOND,
          INTERVAL '2-5' YEAR TO MONTH,
          INTERVAL '1 12:00:01.333' DAY TO SECOND(3)
);

select * from TEST_INTERVAL_TABLE;

REST ENABLING THE TABLE

With the TABLE REST Enabled, I know have a POST call I can make to /ords/admin/test_interval_table, with a JSON Doc on the Request Body representing my new record – and ORDS will do the INSERT for me.

I think I find the ISO format used in the REST API calls easier to work with, yes? It’s definitely less verbose. If you don’t like how the data comes back out, remember you could also cast the INTERVAL back as a string, and get what you want with your SQL.