search

loopbackio / loopback-connector-oracle

Connect Loopback to Oracle
http://loopback.io/doc/en/lb3/Oracle-connector.html
Other
28 stars 30 forks source link

fix: remove UTC offset #188

Closed hacksparrow closed 5 years ago

hacksparrow commented 5 years ago

Removes UTC offset

Description

Related issues

Checklist

ataft commented 4 years ago

What happened to this PR? It doesn't look like the removal of UTC made it into master.

hacksparrow commented 4 years ago

It was for fixing an issue which turned out to be something else.

ataft commented 4 years ago

How did you fix the timezone issue you were having?

I'm having an issue with Oracle only, all the other databases (Postgres, My SQL, SQL Server) work fine. As I understand it, the NodeJS oracledb sets and gets DATE types in local time. Time zone is not stored in the date object, so then getting with UTC results in a time that's 8 hours ahead for me (Standard Pacific Time).

I tried everything from different data types (timestamps) to time zone session settings, nothing worked. Then I found the getUTC logic in dateToOracle() from oracle.js. Removing the UTC part fixed the problem.

Here are the docs that informed me of the way Oracle and the NodeJS driver deal with time zones:

hacksparrow commented 4 years ago

I didn't need to fix it as I was facing this error only on my local machine but it was fine on CI. I assume you'll have to know the timezone of the stored date and offset it with yours on in the result object.

ataft commented 4 years ago

That approach could work as a hack, but is not going to work in a database agnostic environment and would require a lot of manual work. If you read up on the node-oracledb package, you'll understand the following:

DATE, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, and TIMESTAMP WITH TIME ZONE columns are fetched as TIMESTAMP WITH LOCAL TIME ZONE using the session time zone

When this connector forces UTC, it is making a big presumption that is incorrect unless the session time zone is also UTC. Given that loopback-connector-oracle hard codes and forces UTC, I believe that setting process.env.ORA_SDTZ = 'UTC'; will make things work as expected. Preliminary testing shows that it works.

@raymondfeng I'm happy to work with you to fix this connector, or fine to use this workaround if it's too low priority.

fubz commented 3 years ago

Any update on how you got around this @ataft ? I tried setting process.env.ORA_SDTZ = 'UTC' ; however, my Date objects still get offset twice.
I create a timestamp in EST .e.g 12:00 PM EST, this package through loopback converts my time stamp to 17:00 UTC, which the database then interprets as 17:00 EST

ataft commented 3 years ago

@fubz Sorry, setting ORA_SDTZ worked for the few instances I had. You could try forking this repo and making the changes in the PR (i.e. removing UTC from the get() functions).