node-odbc连接Oracle数据库TIMESTAMP值时区偏移问题求助

Hey Raja, great question—this is a common gotcha when working with node-odbc and timezone-agnostic TIMESTAMP fields. Let’s break down why this happens and how you can get the raw, unadjusted value from your Oracle database.

Why node-odbc Adjusts Your TIMESTAMP Values #

The core issue boils down to how node-odbc maps ODBC data types to JavaScript native types:

• Oracle’s TIMESTAMP (without timezone) is returned by the ODBC driver as an ODBC SQL_TIMESTAMP type.
• node-odbc’s default behavior is to convert SQL_TIMESTAMP values into JavaScript Date objects.
• JavaScript Date objects are always timezone-aware: they store the value as UTC, and when you access date/time properties (like getHours()), they automatically convert to the local timezone of your Node.js runtime.

Other ODBC applications (like desktop tools or other language clients) might handle SQL_TIMESTAMP as a raw string or non-timezone-aware data structure, which is why they don’t apply the offset. node-odbc’s choice to use JS Dates is for convenience, but it introduces this unintended timezone adjustment side effect.

How to Get the Unadjusted Raw TIMESTAMP Value #

Here are two reliable approaches to bypass the timezone conversion:

1. Force node-odbc to Fetch TIMESTAMP as a String #

You can configure node-odbc to retrieve TIMESTAMP columns as raw strings instead of parsing them into Date objects. This preserves the exact value stored in the database.

When creating your connection, add the fetchAsString option to specify that SQL_TYPE_TIMESTAMP should be fetched as a string:

const odbc = require('odbc');

async function connect() {
  const connection = await odbc.connect({
    connectionString: 'your-oracle-odbc-connection-string',
    fetchAsString: [odbc.SQL_TYPE_TIMESTAMP] // Key line to disable Date conversion
  });

  const result = await connection.query('SELECT your_timestamp_column FROM your_table');
  console.log(result); // Returns timestamp as a string with no timezone offset
}

Note: Depending on your node-odbc version, the option might be named fetchStringTypes instead—check your installed version’s docs to confirm.

2. Use Oracle SQL to Format TIMESTAMP as a String #

If adjusting the node-odbc config isn’t working, modify your query to explicitly convert the TIMESTAMP to a formatted string using Oracle’s TO_CHAR function. This ensures the result is returned as a string, which node-odbc won’t convert to a Date:

SELECT TO_CHAR(your_timestamp_column, 'YYYY-MM-DD HH24:MI:SS.FF') AS raw_timestamp 
FROM your_table;

The format string YYYY-MM-DD HH24:MI:SS.FF gives you the full timestamp including fractional seconds—adjust it to match your exact needs. This approach is foolproof and works regardless of node-odbc settings.

3. Verify ODBC Driver Settings (Less Likely) #

Since other ODBC apps work correctly, this is probably not the issue, but it’s worth checking: ensure your Oracle ODBC driver isn’t configured to apply timezone conversions. Most drivers leave timezone handling to the application, but double-checking the driver’s configuration panel (e.g., ODBC Data Source Administrator on Windows) can rule out any driver-level overrides.

Wrapping Up #

The main culprit is node-odbc’s default conversion of SQL_TIMESTAMP to JS Date objects, which are timezone-aware. By forcing the value to be fetched as a string (either via node-odbc config or SQL formatting), you can get the exact raw timestamp value from your Oracle database.

内容的提问来源于stack exchange，提问作者raja sekhar