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.

More fun examples, using your own data to learn SQL, build REST APIs, etc.

Untappd || iTunes || Twitter || Strava || Netflix || Spotify


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)"
   ) ;
ELAPSED_TIME 1123…now do maths to turn that into a unit of time…

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.

Using the SQL worksheet in Database Actions, AKA SQL Developer Web.

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 –

That’s “time”: “PT7H3M33S”

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;
Now let’s POST some news rows up.

REST ENABLING THE TABLE

I SHOULD/COULD alias the table…but this is basically a single-click operation.

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.

Whiz-bang, yes? That first interval by the way represents how long Oracle has been a company 🙂

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

1 Comment

Write A Comment