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:
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
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"}}
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"}}}
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 –
And if we go look in our database –
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
OAUTH2 AUTH Request…281ms…nearly 300% faster.
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;
