You’ll at some point need to use a DATE as a predicate to get an item or collection of items out of your Oracle Database REST APIs.

Here’s our least favorite, yet most common example, HR.EMPLOYEES.

HIRE_DATE – when did you start working here?

Let’s build a RESTful Service that lets me pull ALL of the employees hired on a certain DAY.

Step One: Define the Template

Step Zero would be to define the module, but the real work comes into play for how you want to define your REST Service item or collection.

I’ve gone with a MODULE called ‘predicates’ that has a base path of ‘predicates/’ and I decided to have my Template be defined as

dates/:stringdate

I want to retrieve data from my table, so I’m going to do this with SQL, and I’m going to use the GET handler.

Step Two: Add a GET Handler

Just provide the SQL…

In my TEMPLATE I have the text, ‘:stringdate’. When building the ORDS RESTFul Service Handler SQL code block, I can use that exact same string to refer to the value that’s included in the HTTPS Request Uniform Resource Identifier (URI).

As an example, we could do –

SELECT :stringdate FROM dual

And if we saved/published this, and called it with a ‘DATE’ of say, ’21-SEP-89′ –

Don’t yell at me, YET.

You can see that we are getting our string in from the request URI and passing it to our GET handler, all just by using the bind variable :stringdate in BOTH the template definition AND the handler SQL block.

Now, we wanted ALL of the employees who started on the same ‘DAY’ – so we don’t care WHAT TIME they were hired, AND we assume there can be more than ONE, since HIRE_DATE isn’t a KEY or UNIQUE. The other nice thing is we don’t have to worry about NULLs because the column is defined as NOT NULL.

ORDS is going to read in the value off the request URI as a STRING, but we’re going to be using SQL to do a compare based on a DATE field.

DO NOT DO THIS
No, No, NO!

So, we either have to convert HIRE_DATE to a string, OR we have to convert the bind variable to a DATE…or we could live EXTREMELY DANGEROUSLY and let the database implicitly convert the string to a date.

So, what will our SQL look like?

SELECT *
  FROM employees
 WHERE to_char(hire_date, 'DD-MON-YYYY') = :stringdate

You can see that I went with converting the HIRE_DATE column in the database to a string via the TO_CHAR() function.

If this is an INDEXED field, using a function against it, could make the index useless, impacting performance. TEST, TEST, TEST!

I’m writing the SQL, I’m designing the API, so I get to pick the format of the data coming in and out.

My code block is expecting a date with a string format of ‘DD-MON-YYYY.’

So, if I do a GET on predicates/dates/21-SEP-1989 –

Hi Neena, you’ve been working here quite awhile!

But Jeff, I need to use a different date format.

Ok, let’s assume for REASONS that you need to be able to use a ‘/’ when separating the month, day, and year in your URI for the employees.

This is annoying, but not hard.

Two things must change.

Our handler code needs to change:

WHERE to_char(hire_date, 'DD/MON/YYYY') = :stringdate

And when calling the service, we need to URL Encode the string. A ‘/’ is reserved when working with URIs. If I pull up this URI:

/predicates/dates/21/SEP/1989

For it to work, as is, the TEMPLATE would need to be ‘/predicates/dates/:day/:month/:year’ and our HANDLER SQL would look like

WHERE HIRE_DATE = TO_DATE(:day || ‘/’ || :month || ‘/’ || :year, ‘DD/MON/YYYY’)
Baaaaarf. That was hard to type, and it’s going to be hard to understand what I was thinking later.

So to avoid that mess, we can keep our TEMPLATE, and just pass the string with the /’s in them, escaped.

Like so –

The %F is substituted for the / in the URL.

Don’t trust implicit conversions.

That thing above I said ‘NOT TO DO’ – you are going to be tempted to do it. I know this, because I’ve seen it done, MANY times. People email me for help, and one of the first things we look for to explain ‘weird problems with results’ is DATEs and TIMEZONEs.

You CANNOT take it for granted that your database or session NLS_DATE_FORMAT is going to be a particular pattern. As soon as you do, you’re going to be wrong at a certain point, and your APIs won’t work reliably. So BUILD the date format into your query.

Hey Jeff, what’s with the Zulu stuff?

When our EMPLOYEE comes back, their ‘hire_date’ attribute in the JSON response is formatted as date time with timezone, the timezone being UTC. This is how JDBC treats dates, so even when your Oracle DATE doesn’t have a TZ, it gets added. This is a deep/yuck topic, deserving it’s own thread, but for now, let’s say you don’t want that to happen.

Easy answer, do this –

Just a string now…because I told the database to convert the date to a string.

thatjeffsmith
Author

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

2 Comments

  1. Jeff,

    To be honest, I realy like the features ORDS offerst to expose our Oracle DB tables, views and plsql-logic, but the Zulu-way date are handles is still a pain in the… We expose our ORDS services to external parties and we also use our services for mobile usage. The simple fact of getting someones birthdate using an ORDS GET-service, raises a lot of questions from the consumers of our services. They need a simple date (time or time-info is not relevant) but the get a Zulu-formatted date. In many cases it’s hard for them to present the date in the right format. E.g. if someone is born on 2001-01-01 they will get the following birthdate from the ORDS-service “2000-31-12T23:00:00Z”.
    Your proposal to convert the date to a simple format using the to_char function solves the ‘presentation’ problem, but introduces another problem because now you’re not able to use the default filtering options from ORDS. The question ‘Give me all persons born on or after “2000-01-01″‘ cannot be answered anymore, because the ORDS filtering operator “$gt” only works on dates and numbers.
    So my question is if you see any possibility to solve this problem, or do you foresee any future change that makes it possible to work with ‘simple dates’ in ORDS?

    • thatjeffsmith

      What if you added two attributes, one the date as Zulu, and another the char based representation using the format you wanted for display?

Reply To Jonas Cancel Reply