go-sql-driver / go-sql-driver/mysql
Proposal about drive Location and DB Timezone in session
- Dominant language
- Go
- Stars
- 15.3k
- Forks
- 2.3k
- Avg merge
- 2h 23m
- Merged PRs (30d)
- 10
Description
## Problem
We have found that the time difference was happening when validating a time difference
between the moment an action was taken from the user and now, and the data from
the action was stored in the database.
When we were running locally, everything was working perfectly but, after deploy
to staging there was an unexpected error. The system was telling that the difference
was 3 hours more.
After we analyzed the problem we have found that:
- Driver uses, when not set, location from UTC
- Driver does not read the Timezone from the global or session from DB
- MySQL timestamp store data according to Timezone, based on session
- Combined `loc` argument and `timezone` argument from the connection string produced some different behaviors.
### Behavior
You can view the source and run this example [in this repo](https://github.com/felipecaputo/go-mysql-timezone-tests)
ATTENTION: Database is en Europe/Sofia and images are considering America/Sao_Paulo
Considering: both loc and db variable
Local time: 2020-05-11 18:29:54 -0300 -03,
DB time: 2020-05-11 18:29:54 -0300 -03,
Difference Local to DB: 0s
Considering: only loc
Local time: 2020-05-11 18:29:54 -0300 -03,
DB time: 2020-05-12 01:29:54 -0300 -03,
Difference Local to DB: -7h0m0s
Considering: only DB variable
Local time: 2020-05-11 21:29:54 +0000 UTC,
DB time: 2020-05-11 18:29:54 +0000 UTC,
Difference Local to DB: 3h0m0s
Considering: using none configuration
Local time: 2020-05-11 21:29:54 +0000 UTC,
DB time: 2020-05-12 01:29:54 +0000 UTC,
Difference Local to DB: -4h0m0s
### How to reproduce
I've built a simple app that exposes a post endpoint that allows you to post data to DB and read automatically.
There are 4 instances in the `docker-compose.yml` file and a MySQL database in this repo.
Source code from the program is in `main.go`
To run, you just need to run `run.sh` (and probably, need to give execution permissions to it before running `chmod +x run.sh`)
## Proposal
Considering other database drivers, seems plausible that the driver could handle the timezone automatically, based on
some configuration, considering it, and avoiding breaking anyone using the connection as it is now, I think that, creating
a `configTZ` as a `boolean configuration parameter`, that default value will be `false`, to implement the timezone configuration behavior bellow, when set as `true`.
| |||||
|------------|---------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------|
| loc | nil | America/SaoPaulo | nil | America/Sao_Paulo |
| timezone | nil | nil | America/Sao_Paulo | Europa/Sofia |
| Behavior | Set loc to UTC and
DB session to UTC
**OR**
Read from the
database and
set loc to the
same timezone | After connect
to database,
configure session
to America/Sao_Paulo
timezone, and use it
when parsing time | After connect
to database,
configure session
to America/Sao_Paulo
timezone, and use it
when parsing time | Configure as
specified by the
user, but generates
a warning, telling
that TIMEZONE fields
could face a difference
after parse. |
Contributor guide
Assessment
This issue has not been assessed yet.