Working with Dates Using the Node.js Driver

By September 6, 2016 Uncategorized

Node.js is an excellent platform for creating RESTful APIs that store and retrieve data using Oracle Database. node-oracledb is the driver maintained by Oracle that allows Node.js developers to connect to and utilize the database. As data crosses the boundaries between JavaScript and Oracle Database, the driver automatically converts it to native data types on either end. This post explores how that conversion process works for datetime values.

Here’s an overview of what’s covered in this post:

Intro to datetime handling with node-oracledb

SQL execution and bind variables are the most common methods of moving data between Node.js and Oracle Database. When it comes to datetime values, the behavior of node-oracledb is the same regardless of the method used. The section in the documentation on result type mapping describes that behavior:

Internally, TIMESTAMP and DATE columns are fetched as TIMESTAMP WITH LOCAL TIME ZONE using OCIDateTime. When binding a JavaScript Date value in an INSERT statement, the date is also inserted as TIMESTAMP WITH LOCAL TIME ZONE using OCIDateTime.

While not a lengthy explanation, that’s still a lot to take in!

A basic “in bind” example

Bind variables can be thought of as placeholders for values in SQL and PL/SQL code. Just before execution of the code, values can be transferred in (bound in) from the JavaScript context to the code being executed. At the end of execution, values can be transferred back out (bound out) to the JavaScript context. It’s also possible to declare bind variables that work in both directions.

When values are bound in either direction, their data types are converted from one context to another. The following example demonstrates binding a Date in JavaScript into a TIMESTAMP WITH LOCAL TIME ZONE in SQL:

Here’s an overview of what’s going on in the script:

  • Line 3: A variable named myDate is declared and initialized to the datetime value 01-jan-2016 00:00:00.123456 America/New_York. As this is a JavaScript date, the time zone will be picked up from my OS.
  • Line 5: A one-off (non-pooled) connection to the database is obtained from the base class.
  • Line 14: The connection’s execute method is used to execute a SQL statement.
  • Lines 15-18: The SQL statement selects the following:
    • The value of the bind variable – allows us to see the value in the SQL context. TO_CHAR is used to prevent any conversion of the datetime value going back to JavaScript.
    • The session time zone – provides context and so we can see how the driver behaves as the value is changed.
    • A dump of the bind variable – selected so we can verify the data type of the variable.
  • Line 16: A single bind variable is specified as an “in” bind (the default) which maps the value of myDate from JavaScript into the bind variable in_tswltz in the SQL context. The binding process is done just prior to the execution of the statement.
  • Lines 24-27: console.log() is used to show the value of myDate, as well as the values brought back from the execution of the SQL statement.

This is what I get when I run the script on my machine:

Notice that the time zone offset of the date in JavaScript (-05:00) and the time zone offset of the datetime value in Oracle Database (-04:00) were off by an hour. Seem strange? Actually, they are the same datetime value, just seen through different lenses!

The session time zone is the clue to why they look different. My session time zone was implicitly set to a fixed time zone offset of -04:00, which was derived from my OS. When the value of the bind variable was accessed, it reflected the time according to the session time zone.

Finally, the output from the DUMP column confirms that the data type being bound in is TIMESTAMP WITH LOCAL TIME ZONE. This can be verified by looking up the type (231) the table of Oracle Built-in Data Types.

Explicitly setting the session time zone

Folks in the US typically see datetime values through the lens of a time zone region that recognizes daylight savings time rather than a fixed offset. To set the session time zone when using node-oracledb, you can use either the environment variable ORA_SDTZ or an ALTER SESSION statement.

Here’s an example that sets ORA_SDTZ before rerunning the script:

Notice that the value of the TIMESTAMP WITH LOCAL TIME ZONE bind variable now matches the JavaScript date.

If you are unable to use environment variables, or if you want to change the time zone more dynamically, ALTER SESSION may come in handy. Running this script will give me the same output as above:

Keep in mind that using connection.execute() to run an ALTER SESSION statement does incur the cost of a round trip to the database.

The following variation uses ALTER SESSION in the execution of a PL/SQL block. When coding in PL/SQL, we need to use EXECUTE IMMEDIATE to execute Data Definition Language (DDL):

The advantage of using PL/SQL is that we get the flexibility of the ALTER SESSION statement without the additional round trip.

One last option to consider is the use of a logon trigger to set the session time zone. This might be a good option if you can’t use environment variables and you don’t need the flexibility of using ALTER SESSION at the execution level:

Persisting and fetching datetime values

Now that we’ve covered the general behavior of the driver and how you can set the session time zone, let’s explore what happens as we persist datetime values and then later fetch them back out.

We’ll start by creating a table to store the todo object that was created in the first part of this series. We’ll use one column for each of the different datetime data types to store the due property:

Here’s a script that inserts a todo object into the new table:

I’ll run the script after setting the environment variable ORA_SDTZ as follows:

The following script can be used to fetch the value back out. Notice that I needed to cast the TIMESTAMP WITH TIME ZONE value as the driver doesn’t currently support fetching that data type.

This is what I get when I run this script with the same session time zone as when the data was inserted:

Everything looks perfect! 🙂 But what would happen if a user fetched the data out with a different session time zone?

The date values for the DATE and TIMESTAMP columns are now off by a few hours. So what went wrong?

Again, the driver binds in and out using TIMESTAMP WITH LOCAL TIME ZONE. When the data was inserted, the value of the bind variable (in_tswltz) was mapped to the four due_* columns. The value reflected the then current session time zone, which was set to ‘America/New_York’. When the datetime value was converted for storage in the DATE and TIMESTAMP columns, the related time zone information was lost.

When the row was fetched back out, I explicitly converted the TIMESTAMP WITH TIME ZONE COLUMN to TIMESTAMP WITH LOCAL TIME ZONE. This can be done accurately regardless of the session time zone because the actual time zone information needed is included with the data type. However, the DATE and TIMESTAMP columns were implicitly converted to TIMESTAMP WITH LOCAL TIME ZONE, which is done using the session time zone.

Changing the default behavior when fetching datetime values

If you want to change the behavior of fetching datetime values, there are three main options: manual conversion, oracledb.fetchAsString, and fetchInfo. Keep in mind, however, that if you want to use the same ISO 8601 date format that JSON.stringify() uses by default, you may need to convert datetime values to UTC/GMT.

Here’s a logon trigger which sets the NLS_TIMESTAMP_TZ_FORMAT parameter to the ISO 8601 format used by JSON.stringify(). This trigger was enabled while running the examples that follow:

This example uses TO_CHAR to change the default datetime behavior:

In to_char.js, datetime values are manually converted to strings before they reach the driver.

This is what I get when I run the script:

As you can see, the dates have been properly converted to UTC and are formatted as ISO 8601 strings.

If you’d rather not use TO_CHAR, you can use the oracledb.fetchAsString property of the base class. This is a global setting that will affect all bindings.

Executing fetchAsString.js will produce the same output as to_char.js.

In addition to oracledb.fetchAsString, there’s a fetchInfo property of the options object that can be used at the execution level. This property can also be used to override the oracledb.fetchAsString settings by specifying another type or oracledb.DEFAULT.

Putting it all together: An example with Express and node-oracledb

The following example uses Express to create a web server and node-oracledb to interact with Oracle Database. Hopefully, it will provide a better picture of how datetime values move through RESTful APIs. Keep in mind that this is not a complete RESTful API. I’ve only implemented handlers for POST & GET and there’s no authentication, authorization, validations, proper error handling, logging, or instrumentation.

I did, however, create a connection pool because no RESTful API should be using one-off connections! 🙂

I typically run my Node.js servers on GMT/UTC (you’ll have to trust that I’ve set my operating system’s time zone accordingly before running Node.js). I also like to set my session time zone to GMT/UTC whenever possible.

Here’s how I would start the web server:

I can simulate a browser POSTing a todo to the API by opening another terminal and testing via cURL:

The following is an explanation of what happens in the script during a POST:

  1. Express accepts the incoming POST request and applies the JSON bodyParser middleware the app was configured to use (line 17). The JSON bodyParser was configured to use a reviver function (line 18) which will check for and properly parse incoming dates into JavaScript dates.
  2. Express then invokes the handler for the POST request (lines 21-77) and passes along the now parsed todo via req.body.
  3. node-oracledb is used to execute an insert statement on the database (lines 51-75). This can be thought of as a three-step process:
    • Just before execution of the statement, the in bind variables (name and due) are bound into the SQL context. Because name is a String in JavaScript it is bound in as a VARCHAR2 and because due is a Date it is bound in as a TIMESTAMP WITH LOCAL TIME ZONE.
    • The statement is executed.
    • After execution, the value of the out bind (id) is transferred back to the JavaScript context as a Number because that’s how it was configured (line 58).
  4. The execute callback is then invoked (lines 64-74). In the callback, the id of the newly inserted record is attached as a property on the todo in JavaScript. Then res.json() is used to serialize and send the todo back to the client.

If you look back at the cURL command, you’ll see the insert was successful and the new todo’s id was 23.

I can simulate a browser GETing the todo back out as follows.

This is what happens in the script during a GET request:

  1. Express accepts the incoming GET request and invokes the handler (lines 79-125). The id from the URL is passed in via (line 80) and that value is bound into the SQL query (line 97).
  2. The query is executed.
  3. The results from the SQL context are mapped to JavaScript types and used to construct the todo object to be returned to the client (lines 107-113).
  4. Finally, res.json() is used to serialize and send the todo back to the client.

I hope you now have a better understanding of how node-oracledb works with datetime values.

Leave a Reply