Redshift-将时区偏移量(Varchar)添加到时间戳列 [英] Redshift - Adding timezone offset (Varchar) to timestamp column

问题描述

作为ETL到Redshift的一部分，在源表之一中，有2列: original_timestamp-TIMESTAMP :这是在任何区域插入记录的本地时间 original_timezone_offset-Varchar :这是UTC的偏移量

as part of ETL to Redshift, in one of the source tables, there are 2 columns: original_timestamp - TIMESTAMP: which is the local time when the record was inserted in whichever region original_timezone_offset - Varchar: which is the offset to UTC

数据看起来像这样:

original_timestamp      original_timezone_offset
2011-06-22 11:00:00.000000    -0700
2014-11-29 17:00:00.000000    -0800
2014-12-02 22:00:00.000000    +0900
2011-06-03 09:23:00.000000    -0700
2011-07-28 03:00:00.000000    -0700
2011-05-01 01:30:00.000000    -0700

在目标表中，我需要将其转换为UTC(使用偏移量).我该怎么做?到目前为止，我已经尝试了多种方法，但是 dateadd()似乎是最接近的解决方案.但是 dateadd()的问题是，当我说:

In my target table, I need to convert this to UTC (using the offset). How do I do it? So far I have tried multiple things but dateadd() seems to be the closest solution. But the problem with dateadd() is, when I say:

SELECT original_timestamp, original_timezone_offset
 ,dateadd(H, original_timezone_offset, original_timestamp) as original_utc_time

它会将原始时间戳添加/减去"700"/"800"小时，而不是原来的7/8小时，因为偏移量是VARCHAR，其值类似于: -0700 等.

it is adding/subtracting '700'/'800' hours instead of 7/8 hrs to the original timestamp because the offset is a VARCHAR and the values are like: -0700 etc.

有人以前见过这个问题吗?感谢任何帮助/输入.谢谢.

Did anyone see this issue before? Appreciate any help/inputs. Thanks.

推荐答案

只需取偏移量的小时"部分:

Just take the 'hours' part of the offset:

WITH t as (
SELECT  '2011-06-22 11:00:00.000000'::timestamp as original_timestamp, '-0700' as original_timezone_offset
UNION ALL
SELECT '2014-11-29 17:00:00.000000'::timestamp,'-0800'
UNION ALL
SELECT '2014-12-02 22:00:00.000000'::timestamp,'+0900'
)
SELECT
  original_timestamp,
  original_timezone_offset,
  DATEADD(hour, SUBSTRING(original_timezone_offset, 1, 3)::INT, original_timestamp)
FROM t

2011-06-22 11:00:00 -0700   2011-06-22 04:00:00
2014-11-29 17:00:00 -0800   2014-11-29 09:00:00
2014-12-02 22:00:00 +0900   2014-12-03 07:00:00

如果您的非全日制班次偏移(例如+0730)，则需要一些附加的花哨代码.

You'll need some additional fancy code if you have non-full-hour offsets (eg +0730).

这篇关于Redshift-将时区偏移量(Varchar)添加到时间戳列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！