Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
Problem concerns client and server having different timezone.
Actual implementation is using option `timezone`, that can have different type of values:
- 'local' (default) : connector doesn't do any conversion. If the database has a different timezone, there will be an offset issue.
- 'auto' : connector retrieve server timezone. Dates will be converted if server timezone differs from client
- IANA timezone / offset, example 'America/New_York' or '+06:00'.
Problem is that connector does conversion, creating problems with datatype DATE and TIME , and conversion didn't comply well with time function..
Proposal
The only real good solution is to have session using client timezone.
This solve server time function. issue, as well as client side conversion issue putting value in DATE/TIME types.
Timezone option won't change, but in place of conversion, connector will set session timezone :
- 'local': session not set, no conversion done, no additional query on connection creation. This is assuming user knows that client and server share the same timezone
- 'auto': connector will retrieve server timezone. If server timezone differ from client, connector will set session timezone.
- IANA timezone / offset, example 'America/New_York' or '+06:00': connector will set session timezone to explicitly set value, without checking server timezone.
the best default would be 'auto' value, in order to have a correct solution by default, even if not the more efficient, but that would be a breaking change only allowed in a futur 4.x version.
The only issue is that server need having TZ data filled in case client timezone differ from server. In this case, connection will fail on creation.
but it is clear that it is always better to have an explicit immediate error than to have to deal with errors due to erroneous conversions