A reader asked recently, “How to pass date and time parameters in ords query?”

So that’s what I’d like to discuss today, with a few examples.

My goto trick for seeing how to pass something to ORDS, is to first ask ORDS to send the same thing to me.

This way I can see what the expected JSON representation of the data is.

For this post, I’m going to use this table:

I have a DATE, an INTERVAL, and a TIMESTAMP in this table.

And for passing information, we can demonstrate that first with the AUTOREST feature on the table.

I’m going to protect the APIs with a REST Privilege, and I’m going to make sure the ‘SQL Developer’ role has that privilege so I can easily call it with BASIC auth (my database credentials) — more on this later!

GET all the records

Yes, I’m trying yet another REST client (PAW on my Mac).

Let’s not force you to read that tiny text, let’s look at one of the array items in the response.

  "items": [
    {
      "activity_date": "2018-05-19T00:00:00Z",
      "activity_name": "Afternoon Activity",
      "km": 0,
      "description": "8 hrs of washing the house...doors, windows, gutters, siding.",
      "time": "PT8H",
      "activity_date_precise": "2018-05-19T00:00:00.025Z",
      "links": [
        {
          "rel": "self",
          "href": "https://oraclecloudapps.com/ords/admin/temporals/AAAyknAAAAABvSLAAA"
        }
      ]
    },
...

DATEs: Strings with Timezones???

“2018-05-19T00:00:00Z”

Z is for Zulu. Since Greenwich is the site of the “zero meridian,” they called GMT “Zero Time”. Or I guess ‘Zed?’ Zed’s dead baby, Zed’s dead.

Anyway, that’s UTC+0, the time as it would be in Greenwich.

But wait, why would a DATE in Oracle have a timezone (TZ?) TZs are only applied to timestamps, and that’s when it’s a timestamp with a TZ, right?

In the database, yes. But in the {json} universe, dates don’t have a formal specification. However, at Oracle, we have built a JSON standard that we try to maintain across all of our technologies and products. We represent dates as described in the ISO 8601 spec. We also convert these strings to TIMESTAMPS with TZ, and represent everything in Zulu or UTC for GMT+0.

(Docs) Read this, carefully, several times.

GET with a DATE query parameter?

If I want to say only retrieve records with this specific date, then my query parameter would look like this:

{"activity_date":{"$date":"2018-05-19T00:00:00Z"}}
When we pass the DATE to ORDS via the Query Parameter, we need to tell ORDS to expect a date.

Greater than?

The syntax is different than a simple value pair in the above example. It follows the same patterns we’ve shown in a previous post.

{"activity_date":{"$gt":{"$date":"2018-10-01T00:00:00Z"}}}
Manipulating these query parameters by hand is less than fun – but should be easy for your programs 🙂

Passing Dates on RESTful Services (POST BODY)

A very simple scenario – a table with a date, and a POST handler to insert a new record.

CREATE TABLE post_date
 (id INTEGER,
 times DATE);
 
INSERT INTO post_date VALUES (1, sysdate);

And our Module, TEMPLATE, and GET + POST handlers…scroll to the bottom of this story for the source code.

And our call –

String POSTed, DATE goes into the DB, and back out comes the ISO 8601 format.

And if we go look in our database –

It’s the same day and time, as expected.

If you DO see something different than you expect, please ensure that ORDS is running with the UTC timezone, a la

java -Duser.timezeone=UTC -jar ords.war standalone

Let’s Spend a Moment talking about Authentication…

All of my end points here are protected. Because it’s easy, I’ve been using BASIC Auth. And I’m running this scenario in my Always Free Autonomous Cloud Service. That means I’ve been using DATABASE USER credentials to authenticate and authorize my requests.

Here’s what happens on EVERY request:

  • ORDS takes username and password off of request
  • ORDS makes a DATABASE CONNECTION using these credentials
  • If Connection works, keep on trucking, head down the Authorization route
    • pull up service definition in the database
    • pull up ORDS privs
    • check to see if authenticated user has ROLE/Privs required to run the service
  • If Connection doesn’t work, you’re not going to be Authenticated

Making database connections can take time. And when a database gets busy, it can take even more time. In other words, this is VERY expensive – SLOW.

So for ease of use, sure, go for BASIC. But for production use cases, you need to go for OAuth2.

Basic AUTH Request…831ms

Response times will vary, but in the end, this will be slower, significantly slower even.

OAUTH2 AUTH Request…281ms…nearly 300% faster.

Still getting used to the PAW client, it’s got some cool things going for it.

Need a reminder on getting going with OAuth2 (for Autonomous or anywhere else?)

Source for our Simple Module

 
-- Generated by ORDS REST Data Services 21.3.0.r2661100
-- Schema: ADMIN  Date: Fri Oct 22 04:03:38 2021 
--
 
DECLARE
  l_roles     OWA.VC_ARR;
  l_modules   OWA.VC_ARR;
  l_patterns  OWA.VC_ARR;
 
BEGIN
 
  ORDS.DEFINE_MODULE(
      p_module_name    => 'temporal',
      p_base_path      => '/temporal/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'temporal',
      p_pattern        => 'post_date/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'temporal',
      p_pattern        => 'post_date/:id',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select * from POST_DATE where id = :id');
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'temporal',
      p_pattern        => 'post_date/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'temporal',
      p_pattern        => 'post_date/',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select * from POST_DATE');
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'temporal',
      p_pattern        => 'post_date/',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'declare
 new_id integer := :id;
BEGIN
    insert into POST_DATE values (new_id, :times);
    COMMIT;
    :status_code := 201;
    :forward_location := new_id;
END;');
 
 
 
 
COMMIT;
 
END;
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.

Write A Comment