How to get DATE type data in the format of a specific time zone

[shenglong1]

1、What versions are you using?

oracledb version: 6.7.1

oracledb thin mode: false

oracle client version: 21.7.0.0.0

oracle server dbtimezone: +08:00

$node --version

v20.18.2

2、Describe the problem

Thick mode，To execute a stored procedure to query data, there is a column in the data whose type is DATE. Is there a way to make the value of the queried column in the +08:00 time zone or a specific time zone?

3、Include a runnable Node.js script that shows the problem.

CREATE TABLE test_date_table (
    id NUMBER,
    begin_time DATE
);

CREATE OR REPLACE TYPE test_date_table_record AS OBJECT (
    id NUMBER,
    begin_time DATE
);

CREATE OR REPLACE TYPE test_date_table_table AS TABLE OF test_date_table_record;


CREATE OR REPLACE PROCEDURE get_test_date_table_by_range (
    p_start_time IN DATE,
    p_end_time IN DATE,
    p_data OUT test_date_table_table
) IS
BEGIN
    p_data := test_date_table_table();

    FOR rec IN (SELECT id, begin_time 
                FROM test_date_table 
                WHERE begin_time > p_start_time AND begin_time < p_end_time) LOOP
        p_data.EXTEND;
        p_data(p_data.COUNT) := test_date_table_record(rec.id, rec.begin_time);
    END LOOP;
END ;

const oracledb = require("oracledb");
const { to } = require("await-to-js");

let pool = {};

async function executeSql() {
    let connection = await pool.getConnection();
    let sql = `declare
                P_START_TIME DATE;
                P_END_TIME DATE;````
                P_DATA TEST_DATE_TABLE_TABLE;
            begin
                GET_TEST_DATE_TABLE_BY_RANGE(P_START_TIME = :P_START_TIME, P_END_TIME = :P_END_TIME, P_DATA = :P_DATA);
            end;`;

    let p = { 
        P_START_TIME: { type: oracledb.DATE, val: '2025-03-11T11:00:00Z', dir: oracledb.BIND_IN }, 
        P_END_TIME:   { type: oracledb.DATE, val: '2025-03-11T13:00:00Z', dir: oracledb.BIND_IN }, 
        P_DATA:       { type: "TEST_DATE_TABLE_TABLE", dir: oracledb.BIND_OUT },
    }; 
 
    let [e, res] = await to(connection.execute(sql, p));

    console.log(res);
    await connection.commit();
    await connection.close();
}

async function main() {
    let config = {
        username: "",
        password: "",
        connectString: ""
    };
    try {
        oracledb.initOracleClient();
        pool = await oracledb.createPool(config);
        executeSql();
    } catch(e) {
        console.log("exception: ", e)
    }
}

main();

Result:

{
    "P_DATA": [
        {
            "BEGIN_TIME": "2025-03-11T04:00:00.000Z",
            "ID": 13
        }
}

expect result

"BEGIN_TIME": "2025-03-11T12:00:00.000+0800",

Thank you for your help.

[sudarshan12s]

Can you modify your process env like below and confirm if works.
process.env.TZ = 'Asia/Singapore';

For custom formats (+0800 ) from returned UTC format, a converter can be used to change the string format. just an example for using toLocaleString or converting to different format on returned date val.

const result = connection.execute(sql, p,
      [], 
      { fetchTypeHandler: fth }
    );  


function fth(metaData) {
  if ( metaData.dbType === oracledb.DB_TYPE_DATE) {
    return {converter: formatDate};
  }
}

function formatDate(date) {
//  return date.toLocaleString('en-US', { timeZone: 'Asia/Singapore' });
   // or
// const utc_8 = 8 * 60 * 60 * 1000; // UTC+8 in milliseconds
// const newDate = new Date(date.getTime() + utc_8);
//console.log(newDate.toISOString().replace("Z", "+08:00"));
}

[sudarshan12s]

I think I missed the object type part mentioned above.

If you want to modify the value of property, BEGIN_TIME of type TEST_DATE_TABLE_TABLE , the normal fetchTypeHandler passed in execute will not work.

With node-oracledb 6.8.0, dbObjectTypeHandler is introduced which can be set at global level. It has to be set before types are instantiated.

Ex:
 
  const myDbObjectFetchTypeHandler = function(metadata) {
      if (metadata.type === oracledb.DB_TYPE_DATE) {
        return {
          converter: (val) => {
            return val.toLocaleString('en-US', { timeZone: 'Asia/Singapore' });
          }
        };
      }
    };

    oracledb.dbObjectTypeHandler = myDbObjectFetchTypeHandler;

    let [e, res] = await to(connection.execute(sql, p));

Please confirm if this helps .

[shenglong1]

Setting process.env.TZ = 'Asia/Singapore' does not affect the return of DATE type, which always returns the time in UTC timezone.

[shenglong1]

I think I missed the object type part mentioned above.

If you want to modify the value of property, BEGIN_TIME of type TEST_DATE_TABLE_TABLE , the normal fetchTypeHandler passed in execute will not work.

With node-oracledb 6.8.0, dbObjectTypeHandler is introduced which can be set at global level. It has to be set before types are instantiated.

Ex:
 
  const myDbObjectFetchTypeHandler = function(metadata) {
      if (metadata.type === oracledb.DB_TYPE_DATE) {
        return {
          converter: (val) => {
            return val.toLocaleString('en-US', { timeZone: 'Asia/Singapore' });
          }
        };
      }
    };

    oracledb.dbObjectTypeHandler = myDbObjectFetchTypeHandler;

    let [e, res] = await to(connection.execute(sql, p));

Please confirm if this helps .

This works!
Using version 6.8.0, it is capable of handling the DATE type in oracledb.dbObjectTypeHandle.
results:

{ 
    "P_DATA": [
      { 
          ID: 13, 
          BEGIN_TIME: '2025-03-11T12:00:00+08:00' 
      }
}

[sosoba]

{ 
  "BEGIN_TIME": "2025-03-11T12:00:00+08:00" 
}

It's not obvious that the val.toLocaleString('en-US', { timeZone: 'Asia/Singapore' }) returns you the date in ISO format. On my system the result is

3/11/2025, 12:00:00 PM

[sudarshan12s]

{
"BEGIN_TIME": "2025-03-11T12:00:00+08:00"
}

It's not obvious that the val.toLocaleString('en-US', { timeZone: 'Asia/Singapore' }) returns you the date in ISO format. On my system the result is

3/11/2025, 12:00:00 PM

Yes toLocaleString would not get the ISO format required. Additional processing on the returned string with required offset would be needed inside the converter.

one way i could try was below, there may be edge cases to handle :)

const val = new Date('2025-03-11T04:00:00Z');
const options = { timeZone: 'Asia/Singapore', hour12: false};
const datePart = val.toLocaleDateString('en-CA', options); // 'YYYY-MM-DD' format
const timePart = val.toLocaleTimeString('en-GB', options); // 'HH:mm:ss' format

const isoLikeString = `${datePart}T${timePart}+08:00`;
console.log(isoLikeString) // 2025-03-11T12:00:00+08:00

[shenglong1]

Yes, you need to write the functions you need in myDbObjectFetchTypeHandler to provide different formats.