By Dan McGhan, Oracle Developer Advocate
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.
Please Note: This post is part of a series on working with dates in JavaScript, JSON, and Oracle Database. See that post for more details and links to other options.
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:
date-in-bind-example.js JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | var oracledb = require('oracledb');var dbConfig = require('./dbconfig.js'); var myDate = new Date(2016, 00, 01, 00, 00, 00, 123); oracledb.getConnection( dbConfig, function(err, connection) { if (err) {throw err;} connection.execute( "select to_char(:in_tswltz, 'dd-mon-yyyy hh24:mi:ss.ff tzr') as tswltz, " + " sessiontimezone stz, " + " dump(:in_tswltz) dump " + "from dual", { in_tswltz: myDate }, { outFormat: oracledb.OBJECT }, function(err, result) { if (err) {throw err;} console.log('JavaScript date: ', myDate); console.log('result.rows[0].TSWLTZ:', result.rows[0].TSWLTZ); console.log('result.rows[0].STZ: ', result.rows[0].STZ); console.log('result.rows[0].DUMP: ', result.rows[0].DUMP); connection.close(function(err) { if (err) {throw err;}
|