Working with Dates Using the Oracle Node.js Driver

Version 3

    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;}

            });

          }

        );

      }

    );

     

    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 myDatefrom 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:

     

    Shell
    1

     

    2

    3

    4

    5

     

    $ node date-bind-in-example.js

     

    JavaScript date:       Fri Jan 01 2016 00:00:00 GMT-0500 (EST)

    result.rows[0].TSWLTZ: 01-jan-2016 01:00:00.123000000 -04:00

    result.rows[0].STZ:    -04:00

    result.rows[0].DUMP:   Typ=231 Len=11: 120,116,1,1,6,1,1,7,84,212,192

     

    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.

    Remember: With TIMESTAMP WITH LOCAL TIME ZONE, the datetime value is converted to the database time zone and subsequent access reflects the session time zone.

     

    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:

     

     

    Shell
    1

     

    2

    3

    4

    5

    6

     

    $ export ORA_SDTZ='America/New_York'

     

    $ node date-bind-in-example.js

    JavaScript date:       Fri Jan 01 2016 00:00:00 GMT-0500 (EST)

    result.rows[0].TSWLTZ: 01-jan-2016 00:00:00.123000000 America/New_York

    result.rows[0].STZ:    America/New_York

    result.rows[0].DUMP:   Typ=231 Len=11: 120,116,1,1,6,1,1,7,84,212,192

     

    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:

     

     

    26

     

    27

    28

    29

    30

    31

    32

    33

    34

    35

    36

    37

    38

    39

    40

     

    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(

          "alter session set time_zone='America/New_York'",

          function() {

            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;}

                });

              }

            );

          }

        );

      }

    );

     

    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):

     

    JavaScript
    1

     

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

     

    var oracledb = require('oracledb');

     

    var dbConfig = require('./dbconfig.js');

     

    oracledb.getConnection(

      dbConfig,

      function(err, connection) {

        if (err) {throw err;}

     

        connection.execute(

          "begin " +

          "  execute immediate 'alter session set time_zone=''America/New_York'''; " +

          // do work after execute immediate

          "end;",

          function(err, result) {

            if (err) {throw err;}

     

            // continue processing

          }

        );

      }

    );

     

    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:

     

    hr_logon_trigger.sqlOracle PL/SQL

    1

     

    2

    3

    4

    5

    6

     

    create or replace trigger hr_logon_trigger

     

      after logon

      on hr.schema

    begin

      execute immediate 'alter session set time_zone=''America/New_York''';

    end;

    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:

    todos.sql                                                Oracle PL/SQL

    1

     

    2

    3

    4

    5

    6

    7

    8

     

    create table todos(

     

      id         number generated always as identity,

      name       varchar2(100),

      due_d      date,

      due_ts     timestamp,

      due_tswtz  timestamp with time zone,

      due_tswltz timestamp with local time zone

    );

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

     

    insert-todo.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

    36

    37

    38

    39

    40

     

    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(

          "insert into todos ( " +

          "  name, " +

          "  due_d, " + 

          "  due_ts, " +

          "  due_tswtz, " +

          "  due_tswltz " +

          ") values ( " +

          "  :name, " +

          "  :in_tswltz, " +

          "  :in_tswltz, " +

          "  :in_tswltz, " +

          "  :in_tswltz " +

          ")",

          {

            name: "Get milk",

            in_tswltz: myDate

          },

          {

            autoCommit: true

          },

          function(err, result) {

            if (err) {throw err;}

     

            connection.close(function(err) {

              if (err) {throw err;}

            });

          }

        );

      }

    );

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

     

    Shell
    1

     

    2

     

    $ export ORA_SDTZ='America/New_York'

     

    $ node insert-todo.js

    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.

     

    lect-todo.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

     

    var oracledb = require('oracledb');

     

    var myDate = new Date(2016, 00, 01, 00, 00, 00, 123);

    var dbConfig = require('./dbconfig.js');

     

    oracledb.getConnection(

      dbConfig,

      function(err, connection) {

        if (err) {throw err;}

     

        connection.execute(

          "select name, " +

          "  due_d, " +

          "  due_ts, " +

          "  cast (due_tswtz as timestamp with local time zone) due_tswtz, " +

          "  due_tswltz " +

          "from todos",

          {}, // no binds

          {

            outFormat: oracledb.OBJECT

          },

          function(err, result) {

            if (err) {throw err;}

     

            console.log(result.rows[0]);

     

            connection.close(function(err) {

              if (err) {throw err;}

            });

          }

        );

      }

    );

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

     

    1

     

    2

    3

    4

    5

    6

    7

     

    $ export ORA_SDTZ='America/New_York'

     

    $ node select-todo.js

    { NAME: 'Get milk',

      DUE_D: Fri Jan 01 2016 00:00:00 GMT-0500 (EST),

      DUE_TS: Fri Jan 01 2016 00:00:00 GMT-0500 (EST),

      DUE_TSWTZ: Fri Jan 01 2016 00:00:00 GMT-0500 (EST),

      DUE_TSWLTZ: Fri Jan 01 2016 00:00:00 GMT-0500 (EST) }

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

    Shell
    1

     

    2

    3

    4

    5

    6

    7

     

    $ export ORA_SDTZ='America/Los_Angeles'

     

    $ node select-todo.js

    { NAME: 'Get milk',

      DUE_D: Fri Jan 01 2016 03:00:00 GMT-0500 (EST),

      DUE_TS: Fri Jan 01 2016 03:00:00 GMT-0500 (EST),

      DUE_TSWTZ: Fri Jan 01 2016 00:00:00 GMT-0500 (EST),

      DUE_TSWLTZ: Fri Jan 01 2016 00:00:00 GMT-0500 (EST) }

    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 TIMESTAMPcolumns were implicitly converted to TIMESTAMP WITH LOCAL TIME ZONE, which is done using the session time zone.

     

    Remember: Explicitly setting the session time zone to the correct setting when using node-oracledb is very important for consistent behavior, especially when working with DATE and TIMESTAMP data types!

     

    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

    hr_logon_trigger.sql                                                                                                                                 Oracle PL/SQL

    1

     

    2

    3

    4

    5

    6

     

    create or replace trigger hr_logon_trigger

     

      after logon

      on hr.schema

    begin

      execute immediate 'alter session set NLS_TIMESTAMP_TZ_FORMAT=''yyyy-mm-dd"T"HH24:MI:SS.FF3"Z"''';

    end;

    This example uses TO_CHAR to change the default datetime behavior:

    to_char.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

     

    var oracledb = require('oracledb');

     

    var myDate = new Date(2016, 00, 01, 00, 00, 00, 123);

    var dbConfig = require('./dbconfig.js');

     

    oracledb.getConnection(

      dbConfig,

      function(err, connection) {

        if (err) {throw err;}

     

        connection.execute(

          "select name, " +

          "  to_char(from_tz(cast (due_d as timestamp), 'America/New_York') at time zone 'UTC') due_d, " +

          "  to_char(from_tz(due_ts, 'America/New_York') at time zone 'UTC') due_ts, " +

          "  to_char(due_tswtz at time zone 'UTC') due_tswtz, " +

          "  to_char(due_tswltz at time zone 'UTC') due_tswltz " +

          "from todos",

          {}, // no binds

          {

            outFormat: oracledb.OBJECT

          },

          function(err, result) {

            if (err) {throw err;}

     

            console.log(result.rows[0]);

     

            connection.close(function(err) {

              if (err) {throw err;}

            });

          }

        );

      }

    );

     

    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:

     

     

                                                                                        Shell
    1

     

    2

    3

    4

    5

    6

     

    $ node to_char.js

     

    { NAME: 'Get milk',

      DUE_D: '2016-01-01T05:00:00.000Z',

      DUE_TS: '2016-01-01T05:00:00.123Z',

      DUE_TSWTZ: '2016-01-01T05:00:00.123Z',

      DUE_TSWLTZ: '2016-01-01T05:00:00.123Z' }

    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.

    fetchAsString.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

     

    var oracledb = require('oracledb');

     

    var myDate = new Date(2016, 00, 01, 00, 00, 00, 123);

    var dbConfig = require('./dbconfig.js');

     

    oracledb.fetchAsString = [oracledb.DATE];

     

    oracledb.getConnection(

      dbConfig,

      function(err, connection) {

        if (err) {throw err;}

     

        connection.execute(

          "select name, " +

          "  from_tz(cast (due_d as timestamp), 'America/New_York') at time zone 'UTC' due_d, " +

          "  from_tz(due_ts, 'America/New_York') at time zone 'UTC' due_ts, " +

          "  due_tswtz at time zone 'UTC' due_tswtz, " +

          "  due_tswltz at time zone 'UTC' due_tswltz " +

          "from todos",

          {}, // no binds

          {

            outFormat: oracledb.OBJECT

          },

          function(err, result) {

            if (err) {throw err;}

     

            console.log(result.rows[0]);

     

            connection.close(function(err) {

              if (err) {throw err;}

            });

          }

        );

      }

    );

     

    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.

    fetchInfo.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

    36

    37

    38

     

    var oracledb = require('oracledb');

     

    var myDate = new Date(2016, 00, 01, 00, 00, 00, 123);

    var dbConfig = require('./dbconfig.js');

     

    oracledb.getConnection(

      dbConfig,

      function(err, connection) {

        if (err) {throw err;}

     

        connection.execute(

          "select name, " +

          "  from_tz(cast (due_d as timestamp), 'America/New_York') at time zone 'UTC' due_d, " +

          "  from_tz(due_ts, 'America/New_York') at time zone 'UTC' due_ts, " +

          "  due_tswtz at time zone 'UTC' due_tswtz, " +

          "  due_tswltz at time zone 'UTC' due_tswltz " +

          "from todos",

          {}, // no binds

          {

            outFormat: oracledb.OBJECT,

            fetchInfo: {

              DUE_D: {type: oracledb.STRING},

              DUE_TS: {type: oracledb.STRING},

              DUE_TSWTZ: {type: oracledb.STRING},

              DUE_TSWLTZ: {type: oracledb.STRING}

            }

          },

          function(err, result) {

            if (err) {throw err;}

     

            console.log(result.rows[0]);

     

            connection.close(function(err) {

              if (err) {throw err;}

            });

          }

        );

      }

    );

     

     

     

    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!

    demo-server.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

    36

    37

    38

    39

    40

    41

    42

    43

    44

    45

    46

    47

    48

    49

    50

    51

    52

    53

    54

    55

    56

    57

    58

    59

    60

    61

    62

    63

    64

    65

    66

    67

    68

    69

    70

    71

    72

    73

    74

    75

    76

    77

    78

    79

    80

    81

    82

    83

    84

    85

    86

    87

    88

    89

    90

    91

    92

    93

    94

    95

    96

    97

    98

    99

    100

    101

    102

    103

    104

    105

    106

    107

    108

    109

    110

    111

    112

    113

    114

    115

    116

    117

    118

    119

    120

    121

    122

    123

    124

    125

    126

    127

    128

    129

    130

    131

    132

    133

    134

    135

    136

    137

     

    var oracledb = require('oracledb');

     

    var dbConfig = require('./dbconfig.js');

    var express = require('express');

    var bodyParser = require('body-parser');

    var app = express();

    var dateTimeRegExp = /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(\.\d{3})?Z$/;

    var reviver = function(key, value) {

      if (typeof value === 'string' && dateTimeRegExp.test(value)) {

        return new Date(value);

      } else {

        return value;

      }

    };

     

    // The bodyParser middleware accepts a reviver function that it passes along

    // to JSON.parse when the body is parsed.

    app.use(bodyParser.json({

      reviver: reviver

    }));

     

    app.post('/api/todos', function(req, res, next) {

      var todo = req.body;

     

      // This is normally where work, such as validation and manipulation, is done.

      // Becuase of the reviver above, at this point the JSON body has been parsed

      // into native JavaScript types and todo.due is a proper date.

     

      // The todo is now just like it was in the browser, only here, the due date

      // will be reflected in the time zone of the Node.js server's time zone

      // (derived from the OS).

     

      var insertStmt = '' +

        'insert into todos ( ' +

        '  name, ' +

        '  due_d, ' +

        '  due_ts, ' +

        '  due_tswtz, ' +

        '  due_tswltz ' +

        ') values ( ' +

        '  :name, ' +

        '  :due, ' +

        '  :due, ' +

        '  :due, ' +

        '  :due ' +

        ') ' +

        'returning id into :id';

     

      oracledb.getConnection(function(getConnErr, conn) {

        if (getConnErr) {next(getConnErr); return;}

     

        conn.execute(

          insertStmt,

          {

            name: todo.name,

            due: todo.due,

            id: {

              dir: oracledb.BIND_OUT,

              type: oracledb.NUMBER

            }

          },

          {

            autoCommit: true

          },

          function(executeErr, result) {

            if (executeErr) {next(executeErr); return;}

     

            todo.id = result.outBinds.id[0];

     

            conn.release(function(releaseErr){

              if (releaseErr) {next(releaseErr); return;}

     

              res.status(201).json(todo);

            });

          }

        );

      });

    });

     

    app.get('/api/todo/:id', function(req, res, next) {

      var todoId = Number(req.params.id);

      var selectStmt = '' +

        'select id, ' +

        '  name, ' +

        '  due_d, ' +

        '  due_ts, ' +

        '  cast (due_tswtz as timestamp with local time zone) due_tswtz, ' +

        '  due_tswltz ' +

        'from todos ' +

        'where id = :id';

     

      oracledb.getConnection(function(getConnErr, conn) {

        if (getConnErr) {next(getConnErr); return;}

     

        conn.execute(

          selectStmt,

          {

            id: todoId

          },

          {

            outFormat: oracledb.OBJECT

          },

          function(executeErr, result) {

            var todo = {};

     

            if (executeErr) {next(executeErr); return;}

     

            todo.id = result.rows[0].ID;

            todo.name = result.rows[0].NAME;

     

            // For the due property, it's possible to use DUE_D, DUE_TS, DUE_TSWTZ,

            // or DUE_TSWLTZ as they should all be the same except for the fact that

            // only the TIMESTAMP values have subsecond timings.

            todo.due = result.rows[0].DUE_TSWLTZ;

     

            conn.release(function(releaseErr){

              if (releaseErr) {next(releaseErr); return;}

     

              // The json method below will call JSON.stringify on the todo before

              // sending it back down to the client.

              res.status(200).json(todo);

            });

          }

        );

      });

    });

     

    oracledb.createPool(

      dbConfig,

      function(err) {

        if (err) {throw err;}

     

        // Connection pool to db created successfully, open web server

        app.listen(3000, function() {

          console.log('App started on localhost:3000');

        });

      }

    );

     

    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:

     

    Shell
    1

     

    2

    3

     

    $ export ORA_SDTZ='UTC'

     

    $ node demo-server.js

    App started on localhost:3000

     

     

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

     

     

    Shell
    1

     

    2

    3

    4

     

    $ curl -X "POST" "http://localhost:3000/api/todos" \

     

    > -H "Content-Type: application/json" \

    > -d "{\"name\":\"Get milk\",\"due\":\"2016-01-01T05:00:00.123Z\"}"

    {"name":"Get milk","due":"2016-01-01T05:00:00.123Z","id":23}

     

    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.

    Shell
    1

     

    2

     

    $ curl -X "GET" "http://localhost:3000/api/todo/23"

     

    {"id":23,"name":"Get milk","due":"2016-01-01T05:00:00.123Z"}

    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 req.params.id (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.

     

    About the Author

    Over the years Dan McGhan has  watched JavaScript go from a "hacky little language that could" to a robust programming language that does! For the last couple years he's been fascinated with Node.js and single page applications (SPAs) and is keen to see how these technologies can be put to use along with the Oracle database to deliver the next generation of applications. Dan has been an Oracle Database evangelist with Oracle since 2014 with a focus is on JavaScript and HTML5.