MySQL需要在EST中显示时区，但不能更改全局时间设置

Question

我面临一个奇怪的问题。mySQL中的全局时区设置是UTC。mySQL的一个实例中有多个表（子数据库）（我使用database.NET作为管理器）；所以我不能改变全球时区。

以下是我的质疑。我只需要在EST中显示时间。我已经看到了一些解决方案与@sesisionTimeZone，但他们不工作。此外，我正在运行的数据收集到晚上9点东部时间的问题，但在世界协调时，这是在第二天凌晨1点。

我的主要困惑是数据已经存储在UTC表中；然而，我希望看到那些datetime字段显示在EST中。

cartId是数字字段createDate是日期时间字段

我只是在用http://fishcodelib.com/database.htm 并连接到mySQL数据库。

SELECT   DATE(createDate)
    ,DATE_FORMAT(createDate, '%l%p') as HourOfDay
    ,count(cartId) as numCarts_ALL

FROM carts
WHERE createDate >= '2014-09-24' 
      AND createDate < '2014-10-01'
      AND HOUR(createDate) >= 10 AND HOUR(createDate) <21   
GROUP by DATE(createDate),HOUR(createDate)
;

我真的很感激任何帮助。再次感谢

Answer 1

要将datetime从一个时区转换为另一个时区：

SELECT DATE(CONVERT_TZ(createDate, '+00:00', '-04:00')) createdDate,
       HOUR(CONVERT_TZ(createDate, '+00:00', '-04:00')) hourOfDay
FROM carts
WHERE
    createDate BETWEEN CURRENT_DATE + INTERVAL 16 HOUR AND CURRENT_DATE + INTERVAL 26 HOUR
GROUP BY createdDate, hourOfDay

不同的日期

SELECT DATE(CONVERT_TZ(createDate, '+00:00', '-04:00')) createdDate,
       HOUR(CONVERT_TZ(createDate, '+00:00', '-04:00')) hourOfDay
FROM carts
WHERE
    createDate BETWEEN '2014-09-24' AND '2014-10-01'
GROUP BY createdDate, hourOfDay
    HAVING hourOfDay <= 2 OR hourOfDay >= 20

你可能会想"为什么不在WHERE子句中？"

如果where子句中使用了函数，则无法使用索引（这将导致全表扫描）。所以只需过滤掉daterange中的记录，以及HAVING子句中的小时数

供参考

CURRENT_DATE + INTERVAL 16 HOUR

是的缩写

DATE_ADD(CURRENT_DATE, INTERVAL 16 HOUR)

Answer 2

DATETIME数据类型不受全局或连接本地时区设置的影响。这只适用于TIMESTAMP数据类型。这解释了为什么你试图用@@会话混日子。TimeZone没有任何影响。now（）和CURDATE（）受到影响。

您说您的DATETIME数据存储在UTC中。太好了。如果您以这种方式存储数据，生活会轻松得多。

在您使用我将要给您的建议之前，请确保您的MySQL服务器已正确加载其时区表。执行此命令并确保没有得到空结果。

 SELECT CONVERT_TZ(NOW(), 'America/New_York', 'UTC')

如果这不起作用——如果出现NULL或错误——则需要让服务器人员加载时区表。他们应该这样做。他们知道怎么做。（如果他们没有，你应该找一家新的服务提供商。）

我们需要使用名为“America/New_York”的时区，因为您可能希望在每年的适当日期在EDT和EST之间自动切换。

现在，要从表中检索正确转换的UTCDATETIME值，请执行以下操作：

SELECT CONVERT_TZ(createDate, 'UTC', 'America/New_York') AS createDate
  FROM yourTable

这很酷，因为如果用户位于不同的时区，可以将时区设置设置为用户首选项。

要将本地时间值存储为UTC，只需反向执行即可。例如

INSERT INTO yourTable (createDate) VALUES (CONVERT_TZ(?, 'America/New_York', 'UTC'))

现在做

SELECT NOW()

并查看now（）是在当地时间还是在UTC。如果它在UTC中，那么您应该通过执行SETTIME_ZONE='America/New_York'来开始您的会话。这将使您的时区设置正确，以便now（）和CURDATE（）执行您想要的操作。

然后，要从表中获取昨天的（本地时间）行，请执行以下操作：

WHERE createDate >= CONVERT_TZ(CURDATE(),'America/New_York','UTC') - INTERVAL 1 DAY
  AND createDate <  CONVERT_TZ(CURDATE(),'America/New_York','UTC')

这将把当地午夜时间转换为UTC，并获取项目范围。

要获取从今天下午4点到明天凌晨2点的所有数据，您可以执行以下操作：

今天下午4点（本地时间）是CURDATE（），间隔16小时。明天凌晨2点是CURDATE（）间隔26小时或者你可以写它CURDATE（）间隔1天间隔2小时

因此，获取该范围需要：

WHERE createDate >= CONVERT_TZ(CURDATE() + INTERVAL 16 HOUR,'America/New_York','UTC')
  AND createDate <  CONVERT_TZ(CURDATE() + INTERVAL 26 HOUR,'America/New_York','UTC')

如果您愿意将普通日期对象和普通时间对象组合在一起，您还可以使用ADDTime（CURDATE（），'16:00'）来获取今天下午4点的DATETIME值。同样，你可以得到明天凌晨2点这样：

  ADDTIME(CURDATE(),'02:00') + INTERVAL 1 DAY 

注意，这种形式的WHERE子句允许对createDate列上的索引进行范围扫描。这对性能非常好。