如何将时间戳从一个时区转换为另一个时区

Question

我在Teradata中有一个带有TIMESTAMP列的表。

我想将此列中存储的值视为来自"美国太平洋"时区，并将其转换为GMT时间戳。

我试过了，

select  timestamp_col,
    timestamp_col at 'GMT' timestamp_col_gmt,
    timestamp_col at 'America Pacific' timestamp_col_pac,
from table_name;
      timestamp_col          timestamp_col_gmt          timestamp_col_pac
-------------------  -------------------------  -------------------------
2014-10-27 22:02:29  2014-10-27 22:02:29+00:00  2014-10-27 15:02:29-07:00
2013-11-28 22:55:27  2013-11-28 22:55:27+00:00  2013-11-28 14:55:27-08:00
2014-09-19 00:23:56  2014-09-19 00:23:56+00:00  2014-09-18 17:23:56-07:00
2013-06-18 00:39:18  2013-06-18 00:39:18+00:00  2013-06-17 17:39:18-07:00

但看起来，它认为时间戳col最初是在GMT。

我想要的是这样的东西。

      timestamp_col    timestamp_col_gmt  
-------------------  -------------------
2013-11-28 22:55:27  2013-11-29 06:55:27  --date belongs to PST. 8 hour difference
2014-10-27 22:02:29  2014-10-28 05:02:29  --date belongs to PDT. 7 hour difference
2014-09-19 00:23:56  2014-09-19 07:23:56  --date belongs to PDT. 7 hour difference
2013-06-18 00:39:18  2013-06-18 07:39:18  --date belongs to PDT. 7 hour difference

我也想考虑夏令时。

使用dnoeth的查询，在时区切换期间的某个时间，结果是不正确的。

with recursive y(timestamp_col) as
(
    select timestamp_val
    from x
    union all
    select timestamp_col + interval '1' hour
    from y
    where timestamp_col <= timestamp'2015-03-08 10:00:00'
),
x(timestamp_val) as 
(
    select timestamp'2015-03-08 00:00:00'
)
select timestamp_col,
cast(
    (timestamp_col at 'America Pacific') 
    - (extract(timezone_hour from (timestamp_col at 'America Pacific')) * interval '1' hour) 
as timestamp(0))    timestamp_col_gmt
from y
order by timestamp_col;
      timestamp_col    timestamp_col_gmt
-------------------  -------------------
2015-03-08 00:00:00  2015-03-08 08:00:00    --PST, correct
2015-03-08 01:00:00  2015-03-08 09:00:00    --PST, correct
2015-03-08 02:00:00  2015-03-08 10:00:00    --Can be ignored
2015-03-08 03:00:00  2015-03-08 11:00:00    --PDT, should be 10:00:00
2015-03-08 04:00:00  2015-03-08 12:00:00    --PDT, should be 11:00:00
2015-03-08 05:00:00  2015-03-08 13:00:00    --PDT, should be 12:00:00
2015-03-08 06:00:00  2015-03-08 14:00:00    --PDT, should be 13:00:00
2015-03-08 07:00:00  2015-03-08 15:00:00    --PDT, should be 14:00:00
2015-03-08 08:00:00  2015-03-08 16:00:00    --PDT, should be 15:00:00
2015-03-08 09:00:00  2015-03-08 17:00:00    --PDT, should be 16:00:00
2015-03-08 10:00:00  2015-03-08 17:00:00    --PDT, correct
2015-03-08 11:00:00  2015-03-08 18:00:00    --PDT, correct
with recursive y(timestamp_col) as
(
    select timestamp_val
    from x
    union all
    select timestamp_col + interval '1' hour
    from y
    where timestamp_col <= timestamp'2015-11-01 10:00:00'
),
x(timestamp_val) as 
(
    select timestamp'2015-11-01 00:00:00'
)
select timestamp_col,
cast(
    (timestamp_col at 'America Pacific') 
    - (extract(timezone_hour from (timestamp_col at 'America Pacific')) * interval '1' hour) 
as timestamp(0))    timestamp_col_gmt
from y
order by timestamp_col;
      timestamp_col    timestamp_col_gmt
-------------------  -------------------
2015-11-01 00:00:00  2015-11-01 07:00:00    --PDT, correct
2015-11-01 01:00:00  2015-11-01 08:00:00    --PDT, correct
2015-11-01 02:00:00  2015-11-01 09:00:00    --PST, should be 10:00:00
2015-11-01 03:00:00  2015-11-01 10:00:00    --PST, should be 11:00:00
2015-11-01 04:00:00  2015-11-01 11:00:00    --PST, should be 12:00:00
2015-11-01 05:00:00  2015-11-01 12:00:00    --PST, should be 13:00:00
2015-11-01 06:00:00  2015-11-01 13:00:00    --PST, should be 14:00:00
2015-11-01 07:00:00  2015-11-01 14:00:00    --PST, should be 15:00:00
2015-11-01 08:00:00  2015-11-01 15:00:00    --PST, should be 16:00:00
2015-11-01 09:00:00  2015-11-01 17:00:00    --PST, correct
2015-11-01 10:00:00  2015-11-01 18:00:00    --PST, correct
2015-11-01 11:00:00  2015-11-01 19:00:00    --PST, correct

Answer 1

Teradata存储标准化为GMT/UTC/00:00的时间戳，并根据会话时区显示这些时间戳。您的系统或用户似乎已设置为GMT作为默认值。

这应该返回预期的数据：根据"美国太平洋"时区获取数据，减去时区小时，并将其投射回时间戳。

CAST((timestamp_col AT 'America Pacific')
        - (EXTRACT(TIMEZONE_HOUR FROM (timestamp_col AT 'America Pacific')) * INTERVAL '1' HOUR)
     AS TIMESTAMP(0))

编辑：

这将返回切换到DST:的正确值

CAST(
    (timestamp_col AT 'America Pacific') 
    - (EXTRACT(TIMEZONE_HOUR FROM ((timestamp_col + INTERVAL '7' HOUR) AT 'America Pacific')) * INTERVAL '1' HOUR) 
AS TIMESTAMP(0))    timestamp_col_gmt

当它应用于从DST:切换回来时

2014-11-02 01:00:00  2014-11-02 08:00:00
2014-11-02 02:00:00  2014-11-02 10:00:00