Redshift - Добавление смещения часового пояса (Varchar) в столбец отметки времени

как часть ETL для Redshift, в одной из исходных таблиц есть 2 столбца: original_timestamp - TIMESTAMP: это местное время, когда запись была вставлена ​​в любой регион original_timezone_offset - Varchar: который является смещением относительно 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() в том, когда я говорю:

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

он добавляет / вычитает '700' / '800' часов вместо 7/8 часов к исходной метке времени, потому что смещение представляет собой VARCHAR, а значения такие: -0700 и т. д.

Кто-нибудь видел эту проблему раньше? Цените любую помощь / вклад. Спасибо.


amazon-web-services amazon-redshift timezone-offset
person Vamsi    schedule 12.03.2019    source источник
comment
текст столбца смещения?   -  person Jon Scott    schedule 13.03.2019
comment
Да, Джон. Это текст   -  person Vamsi    schedule 13.03.2019

Ответы (2)


arrow_upward
1
arrow_downward

Просто возьмите часть смещения "часы":

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).

person John Rotenstein    schedule 12.03.2019

arrow_upward
0
arrow_downward

Во-первых, осознайте, что если ваши временные метки уже находятся в местном времени данного смещения, вам нужно вычесть это смещение, чтобы преобразовать обратно в UTC. В первом приведенном вами примере 2011-06-22 11:00:00 -0700 эквивалентно 2011-06-22 18:00:00 UTC.

Однако вместо того, чтобы пытаться самостоятельно складывать или вычитать эти значения, вы должны позволить AT TIME ZONE сделает всю работу за вас. Он создаст timestamptz, который находится в предоставленном вами смещении, затем вы можете снова использовать его для преобразования в UTC.

(Обратите внимание, что вместо этого вы можете использовать функцию CONVERT_TIMEZONE, но это понимает только Redshift, где AT TIME ZONE также работает с обычным PostgreSQL.)

Однако у вас есть смещения часовых поясов, которые у вас не в формате, понятном для этих функций. См. примечания по использованию часовых поясов. Итак, прежде чем мы попытаемся преобразовать, давайте переведем ваши строки смещения в понятный формат.

Мы хотим, чтобы -0700 стал +07:00. Двоеточие является обязательным, а знак должен быть перевернут, поскольку он будет интерпретироваться в формате часового пояса в стиле POSIX. В этом формате положительные значения лежат запад от GMT вместо обычных соглашений, определенных в ISO 8601.

concat(translate(substring(original_timezone_offset, 1, 3), '-+', '+-'),':',substring(original_timezone_offset, 4, 2))

Затем мы будем использовать это с AT TIME ZONE для преобразования:

(original_timezone AT TIME ZONE <the above mess>) AT TIME ZONE 'UTC' AS utc_timestamp

Собираем все вместе ...

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,
  concat(translate(substring(original_timezone_offset, 1, 3), '-+', '+-'),':',substring(original_timezone_offset, 4, 2)) as modified_timezone_offset,
  (original_timestamp AT TIME ZONE concat(translate(substring(original_timezone_offset, 1, 3), '-+', '+-'),':',substring(original_timezone_offset, 4, 2))) AT TIME ZONE 'UTC' AS utc_timestamptz
FROM t

Выход:

2011-06-22 11:00:00  -0700  +07:00  2011-06-22 18:00:00
2014-11-29 17:00:00  -0800  +08:00  2014-11-30 01:00:00
2014-12-02 22:00:00  +0900  -09:00  2014-12-02 13:00:00

SQL Fiddle здесь.

person Matt Johnson-Pint    schedule 15.03.2019