如何根据列中当前存在的时间值从 UTC 转换为 EST？

Question

我想使用SQL将start和end列上的时间从UTC转换为EST。经过数小时的搜索，我在网上找到了以下查询，但没有成功(以下查询不正确(。

有没有一种干净的方法可以根据字段的日期从UTC转换为EST？

start列具有2019-06-01 05:00:00的当前值

end列具有2019-06-30 04:00:00的当前值

我希望这些在美国东部时间。

提前谢谢。

UPDATE my_table
SET start = from_tz(to_timestamp('2009-11-17 18:40:05','yyyy-mm-dd hh24:mi:ss'), 'UTC'), end = from_tz(to_timestamp('2009-11-17 18:40:05','yyyy-mm-dd hh24:mi:ss'), 'UTC') 
AT TIME zone 'America/New_York'
WHERE id = 3;

Answer 1

您可以使用CONVER_TZ

您可以使用"+00:00"，这样服务器就不必查找它，这将节省时间，就像ysth在评论中解释的那样

SELECT
CONVERT_TZ('2007-03-11 2:00:00','UTC','America/New_York') AS time1,
CONVERT_TZ('2007-03-11 3:00:00','UTC','America/New_York') AS time2;

time1|time2：------------------|：------------------2007-03-10 21:00:00|2007-03-10 22:00:00

<blockquote\

UPDATE my_table
SET start = CONVERT_TZ('2009-11-17 18:40:05','UTC','America/New_York')
, end = CONVERT_TZ('2009-11-17 18:40:05','UTC','America/New_York') 
WHERE id = 3;

>

表'db_477873842.my_Table'不存在

<blockquote\

SELECT
CONVERT_TZ('2007-03-11 2:00:00','+00:00','America/New_York') AS time1,
CONVERT_TZ('2007-03-11 3:00:00','+00:00','America/New_York') AS time2;

>

time1|time2：------------------|：------------------2007-03-10 21:00:00|2007-03-10 22:00:00

<blockquote\

UPDATE my_table
SET start = CONVERT_TZ('2009-11-17 18:40:05','+00:00','America/New_York')
, end = CONVERT_TZ('2009-11-17 18:40:05','+00:00','America/New_York') 
WHERE id = 3;

>

表'db_477873842.my_Table'不存在

db<gt；小提琴这里