与SQL进行时区协调 [英] timezone reconciliation with SQL

问题描述

我有许多要连接的 MySQL 数据库，并希望确保查询之间的时间一致性.因此，例如，这些服务器之一当前位于 CDT 时区.

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              |
+---------------------+--------------------+--------------------+---------------------+

注意:我们现在在 DST 中，所以它是 CDT .我假设，它将在 DST 之外时自动更改为 CST ，对吗?

Note: We're in DST right now, so it's CDT. I'm assuming this would automatically change to CST when outside DST, right?

因此，根据以上知识，我的 DSN 后缀看起来像这样:

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?

因此， go 中是否有一种编程方式可以将3个字母的代码 CDT 映射到更正式的时区名称，例如 America/Chicago .

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.

上面提供了一个鸡/蛋的场景:为了确定远程服务器的时区，需要连接/查询服务器；知道这一点后，DSN参数可能会为将来的调用而更改.

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.

事实之后是否可以更改DSN参数，还是需要创建全新的连接 sql.DB 连接池?

Can DSN parameters be changed after the fact, or does a brand new connection sql.DB connection pool need to be created?

您可能会问，为什么要检查时区是否已更改-它不是静态的吗?在负载平衡的数据库情况下应该做什么?理论上两个副本可以在不同的时区吗?

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?

是否应将所有带有时间戳记的列都用SQL UNIX_TIMESTAMP()包装以标准化数据并避免这种麻烦?

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.

推荐答案

1/3

go 使用IANA的时区数据库和精确的时区名称.试图逆向工程MySQL如何从(Linux)主机确定本地时区格式并在 go 客户端中复制该逻辑(如@MattJohnson所指出的那样)被证明是不可靠的.

1/3

go uses IANA's Time Zone Database with precise zone names. Trying to reverse engineer how MySQL determines the local timezone format from a (Linux) host and duplicate that logic in a go clients - as @MattJohnson pointed out - proves to be unreliable.

database/sql.DB -将对所有连接使用相同的 DSN .虽然 sql.DB 本来应该创建一次并使用多次-但事后没有办法更改 DSN -因此，需要创建一个品牌更改 DSN 时新的 sql.DB .

database/sql.DB - created via Open(drv, DSN) - will use the same DSN for all connections. While an sql.DB is meant to be created once and used many times - there is no way to change the DSN after the fact - so one would need to create a brand new sql.DB when changing the DSN.

因此，更好的办法似乎是利用 MySQL 将所有 datetime 值从本地转换为UTC时区，然后再传输给客户端.这消除了通过 DSN 在连接时设置数据库(可能未知)时区的麻烦.

So the better tack, appears to leverage MySQL to convert all datetime values from local to UTC timezone before transmission to the client. This removes the complication of setting the database's (possibly unknown) timezone at connection time via the DSN.

一个有希望的选择是设置连接的会话时区:

One promising option, is to set the connection's session timezone:

• SET @@ session.time_zone ="+00:00";
• 但是，这仅适用于 current 连接(在连接池中).但是， go 客户端在任何给定时间都不知道他们可能正在使用哪个免费连接.
• 因此，要确保此操作始终有效，您需要在 所有查询 之前手动应用它.即使仅使用一个数据库连接-如果连接失败并且连接重试开始-任何先前的会话状态都将丢失.

• SET @@session.time_zone = "+00:00";
• however, this only works for the current connection (within the connection pool). A go client however will not know which free connection they may be using at any give time.
• So to ensure this always works, one would need to apply it manually before all queries. Even if only one DB connection is in use - if the connection fails and connection retry kicks in - any previous session state would be lost.

因此，用如下转换函数包装所有 datatime 列:

So instead, wrapping all datatime columns with a conversion function like so:

CONVERT_TZ(`STAMP_UPDATED`,@@session.time_zone,'+00:00')

确保时区计算在查询时完成，并且在连接重新连接等过程中不会丢失.

ensures the timezone calculation is done at query time and will not be lost during a connection reconnection etc.

因此，现在 DSN 不再需要指定 loc -因为默认是 UTC .实际上， DSN 仅需要?parseTime = true 的后缀选项，以允许将 datetime 转换为 go 的本机 time.Time .

So now the DSN no longer needs to specify loc - as UTC is the default. In fact the DSN only needs the suffix option of ?parseTime=true to allow the datetime to be translated into go's native time.Time.

最后也是最重要的一点，这将适用于设置为任何时区的任何服务器.

Finally and most importantly, this will work with any server set to any timezone.

向此 answer 的提示.

这篇关于与SQL进行时区协调的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！