Tedious: useUTC=false does not seem to work

[RevealedFrom]

This seems to be similar to #675 , which is closed.

I am using the latest 5.1.0 node-mssql (tediousjs/node-mssql).

My SQL Server is version 2014.12.500 and the time zone is at UTC+8:

select sysdatetimeoffset()
2019-07-25 10:41:08.4667360 +08:00

and sample test data of a DATETIME type using select Date from cdrs where id=22 is:

2019-07-24 23:20:00.000

My Node machine is on UTC+8 too:

> (new Date()).getTimezoneOffset()
-480

and my Node code is:

const id = 22;
POOL.request().query`SELECT Date from cdrs where id=${id}`.then(
  (results) => {
    console.log(results.recordset[0]);
});

That log statement gives:

Thu Jul 25 2019 07:20:00 GMT+0800 (Singapore Standard Time)

which is the date in the database treated as in UTC.

I get the same results whether useUTC is set to false or not. (I set useUTC in the configuration option to open a database connection. I hope this is the right place.)

How do I get the same time when both my SQL Server and Node server are in the same time zone?

[IanChokS]

Hi @RevealedFrom,

It looks like the latest release from mssql is still using tedious version 6.1.0 in its package.json. The latest release for tedious is 6.2.0. Perhaps try upgrading the tedious from version 6.1.0 to 6.2.0 in the mssql package.json. Hopefully that fixes the issue :)

[RevealedFrom]

Oh! It seems mssql says it's even older:

"tedious": "^4.2.0"

[IanChokS]

Did upgrading to the latest Tedious version solve your issue? :)

[RevealedFrom]

Doesn't a semver of ^4.2.0 mean it is not compatible with version 6?

[IanChokS]

Yes, 4.x.x does not upgrade automatically to 6.x.x. The '^' symbol only upgrades the last two values (ex. it can change the values of x here: 4.x.x) and not the first number. You can read more about it here: (about semantic versioning) https://docs.npmjs.com/about-semantic-versioning

To automatically get updates for the latest one, you need to manually change the first number. In this case, from 4 -> 6

[arthurschreiber]

Doesn't a semver of ^4.2.0 mean it is not compatible with version 6?

Yes, that's what it means. But we try to keep breaking changes as small as possible, and they're documented with each major release, so upgrading from 4.x to 6.x should not be impossible. 😬

Anyway, I don't think this is fixed in 6.x, and is a real issue. 😟

[IanChokS]

@RevealedFrom any updates? Is it still buggy after upgrading tedious? Also could you show the full use case you tried so I can try to reproduce the issue? Thanks

[RevealedFrom]

@IanChokS Thanks for helping.

I dare not, and also haven't found out how to undo should it not work, upgrade tedious to 6.1. I did a workaround by adding back the time zone offset after getting the data from SQL:

then(results => {
  const tz = (new Date()).getTimezoneOffset();
  results.recordset.forEach(c => {
    c.Date = new Date(c.Date.getTime() + tz * 60000);
  });

The details of the use case as as in the code in my original post. The variable POOL is the result from the ConnectionPool connector.

[IanChokS]

@RevealedFrom, sorry I'm still looking for the bug. I tried toggling on/off with the 'useUTC' options on the Tedious project (test/unit/data-types.js), and with the node-mssql project (test/tedious/tedious.js), and the result of toggling with it does seem to effect the tests for both - so setting the UTC options in the configuration object is changing the DateTime. So I'm still unsure of where the bug lies in your case.

@arthurschreiber, any ideas?

[RevealedFrom]

I have just repeated toggling back and forth and the time from the query is always the same.

I am not certain whether I am setting the useUTC parameter in the right place. Is the following correct?

const config = {
 server: process.env.sqlHost,
 port: process.env.sqlPort ? Number(process.env.SqlPort) : 1433,
 user: process.env.sqlUser,
 password: process.env.sqlPassword,
 database: process.env.sqlDb,
 pool: { max: 10, min: 0, idleTimeoutMillis: 30000 },
 useUTC: false
};

const pool = new sql.ConnectionPool(config);
pool.connect((err) => { ...

[IanChokS]

Ahh, it looks like you're missing the options field! Try putting the useUTC field in an options field, such as:

const config = {
  "server": process.env.sqlHost,
 "port": process.env.sqlPort ? Number(process.env.SqlPort) : 1433,
 "user": process.env.sqlUser,
 "password": process.env.sqlPassword,
 "database": process.env.sqlDb,
 "pool": { max: 10, min: 0, idleTimeoutMillis: 30000 },
 "options":{
        "useUTC": false
    }
}

In the test, the useUTC options is located in the config.options field:

See if that works?

[RevealedFrom]

Yes, it works as expected now! Thanks for the help, and sorry for the wild goose chase.