Question 1/3:
I have a number of MySQL
databases to connect to and want to ensure time consistency across queries. So for example, one of these servers is currently in the CDT
timezone.
> select CURRENT_TIMESTAMP, @@system_time_zone, @@global.time_zone, @@session.time_zone;
+---------------------+--------------------+--------------------+---------------------+
| CURRENT_TIMESTAMP | @@system_time_zone | @@global.time_zone | @@session.time_zone |
+---------------------+--------------------+--------------------+---------------------+
| 2019-05-31 09:44:45 | CDT | SYSTEM | SYSTEM |
+---------------------+--------------------+--------------------+---------------------+
Note: We're in DST
right now, so it's CDT
. I'm assuming this would automatically change to CST
when outside DST
, right?
So with the above knowledge, my DSN
suffix looks something like this:
...?parseTime=true&loc=America%2FChicago // i.e. 'America/Chicago' - maybe 'CST6CDT' would work too?
So, is there a programatic way in go
to map from the 3-letter code CDT
to the more formal timezone names like America/Chicago
.
2/3:
The above presents a chicken/egg scenario: in order to determine the remote server's timezone, one needs to connect/query the server; with that knowledge the DSN parameters may change for future calls.
Can DSN parameters be changed after the fact, or does a brand new connection sql.DB
connection pool need to be created?
3/3:
You may ask, why check to see if the timezone has changed - isn't it static? What should one do in a Load-Balanced DB situation? Two replicas could in theory be in differing timezones?
Should all columns with timestamps just be wrapped with an SQL UNIX_TIMESTAMP()
to normalize the data and avoid this headache?
I could get into time-drifts too, but I'll stop here for now.