选择时间戳字段时绕过MySQL自动GMT转换

Question

互联网上有很多关于转换MySQL时间戳的东西，它是如何工作的等等。但我需要的是相反的：知道MySQL在时间戳字段中将每个日期时间数据存储为UTC，我只想直接检索存储的UTC值，而不让MySQL在日期时间与系统/服务器/连接时区之间混淆。

你看，我们已经建立了一个简单的节点。js feeder从几个第三方MySQL数据库读取数据（因此我无法更改它们的时区设置），并将收集的数据保存到Elasticsearch，作为“非规范化过程”。由于原始数据来自不同的时区，我需要将它们存储在UTC中，这样我就可以轻松地协调进一步的GET和聚合。

我知道我可以动态设置连接时区，我知道我可以更改在我的节点应用程序中获取的每个时间戳字段，但既然MySQL引擎已经将时间戳存储在UTC中，如果我可以直接获取它，而无需转换函数或昂贵的数据处理，为什么还要添加任何其他步骤？

简而言之，我想知道：有没有办法绕过MySQL自动GMT转换？

Answer 1

看起来没有办法从MySQL字段中获取原始UTC值；每个函数都使用时区设置，无论是SYSTEM还是您配置的任何其他函数。

MySQL强制您使用日期转换的方式至少非常严格。例如，假设您将MySQL服务器设置为GMT-03:00和GMT/DST-02:00的时区，并且您存储了类似“2016-07-01 10:00:00”的日期时间。如果您在DST结束后选择此值，您将获得“2016-07-01 09:00:00”。

除非单独存储GMT偏移量，或者之前知道服务器存储时的时区，否则无法确定现在是几点。

我们使用了第二种方法。我们保存了服务器时区，并使用它来计算偏移量并返回ISO datetime，因此可以轻松地进行未来的计算。

DROP FUNCTION IF EXISTS `iso_datetime`;;

CREATE FUNCTION `iso_datetime` (
    p_datetime TIMESTAMP
) RETURNS VARCHAR(25)

READS SQL DATA

BEGIN

    DECLARE _timezone VARCHAR(255) DEFAULT NULL;
    DECLARE _offset VARCHAR(6) DEFAULT NULL;

    SET _timezone = (SELECT timezone FROM network);
    SET _offset = (SELECT SUBSTRING(TIMEDIFF(p_datetime,CONVERT_TZ(p_datetime, _timezone,'UTC')), 1,6));

    RETURN CONCAT(DATE_FORMAT(p_datetime, '%Y-%m-%dT%H:%i:%S'), _offset);

END;

为此，您必须将时区信息加载到MySQL中，以便服务器可以为您计算日期的tz偏移量。

Answer 2

MySQL提供了一个UNIX\u TIMESTAMP函数，该函数返回原始整数值。这根本不受时区转换的影响。

SELECT UNIX_TIMESTAMP( timestamp_col ) ...

但这返回一个原始整数，而不是日期时间。如果需要，客户端需要将其转换为“日期时间”类型对象。

另一种选择是使用CONVERT_TZ函数从会话time_zone设置转换为UTC。

 SELECT CONVERT_TZ( timestamp_col, @@session.time_zone,'+00:00')

但是，这并不能真正“绕过”时区转换。这种方法的一个缺点是，如果会话时区受到夏令时变化的影响，则存在模糊性，即时钟每年“倒转”一个小时。e、 g.2015年11月1日星期日凌晨2点CDT过渡到2015年11月1日星期日凌晨1点CST。（从UTC转换回来，如果我们在会话时区中得到凌晨1:30，我们不“知道”这是CDT还是CST。而转换回UTC也不知道它是哪一个。）

另一个选项（我想您已经提到）是将会话时区更改为UTC。然后您可以将timestamp\u col值返回为UTC。您可以保存当前时区设置，并在完成后将其设置回原处，例如：。

SET @save_session_time_zone := @@session.time_zone ;
SET time_zone = '+00:00' ; 
SELECT timestamp_col ... 
SET time_zone = @save_session_time_zone ;

但是，当MySQL数据库会话的time_zone与客户端的time_zone不匹配时，您的客户端连接器可能会进行一些不太有用的转换，就像JDBC驱动程序（MySQL Connector/J）所做的时髦恶作剧一样。（这种担心不仅限于返回UTC；每当客户端的time_zone与数据库会话的time_zone不匹配时，这就是一个问题。）