Блог

Главная - Вопросы по программированию - Как проанализировать столбец даты, если записи также имеют часовой пояс в AWS Athena?

Как проанализировать столбец даты, если записи также имеют часовой пояс в AWS Athena?

#datetime #timezone #presto #amazon-athena

#datetime #Часовой пояс #presto #amazon-athena

Вопрос:

Я пытаюсь преобразовать столбец даты строкового типа в тип даты в AWS Athena. В этом столбце содержатся записи формата %m-%d-%Y или %m-%d-%Y %H:%i:%s

Я использую приведенный ниже запрос в AWS Athena для решения вышеуказанных проблем:

 SELECT col1, col2,
   Coalesce(
        try(date_parse(replace("col3", '/', '-'), '%m-%d-%Y %H:%i')),
       try(date_parse(replace("col3", '/', '-'), '%m-%d-%Y')),
     try(date_parse(replace("col3", '/', '-'), '%Y-%m-%d %H:%i:%s'))
) AS date_col
FROM "db"."table"

Этот запрос отлично работает для вышеупомянутых форматов. Но есть некоторые записи с часовым поясом, такие как 3/10/2019 11:31 PM EDT . Я не могу проанализировать это, поэтому эти записи просто отображаются как NULL.

Я пробовал try(date_parse(replace("col3", '/', '-'), '%m-%d-%Y %H:%i %p %z')) , но это тоже не сработало. Есть ли способ сделать это с date_parse() ? Я также пробовал что-то вроде parse_datetime("col3", 'MM/DD/YYYY' 'HH:mm' 'P' 'Z') , но это тоже не сработало. Есть ли другой обходной путь для этого или я что-то здесь упускаю?

Ответ №1:

Вы можете проанализировать его с помощью следующего шаблона для parse_datetime функции: MM/dd/YYYY hh:mm a z . Формат шаблона основан на времени Joda.

Например:

 WITH t(x) AS (VALUES '3/10/2019 11:31 PM EDT')
SELECT parse_datetime(x, 'MM/dd/YYYY hh:mm a z') 
FROM t

выдает:

                   _col0
------------------------------------------
 2019-03-10 23:31:00.000 America/New_York

Комментарии:

1. Я попробовал это, и это не удалось с этой ошибкой NOT_SUPPORTED: Unsupported Hive type: timestamp with time zone.

2. Если вы пытаетесь вставить результат в другую таблицу, вам нужно преобразовать его в другой поддерживаемый тип (например, TIMESTAMP). Обратите внимание, что Athena основана на очень старой версии Presto, выпущенной более 3 лет назад, и некоторые функции в последних версиях Presto могут отсутствовать там.

3. Я пытался сделать Coalesce( try(date_parse(replace("col3", '/', '-'), '%m-%d-%Y %H:%i')), try(CAST(parse_datetime(replace("col3", '/', '-'),'MM/dd/YYYY hh:mm a z') AS TIMESTAMP)) , но все строки с часовым поясом были просто преобразованы в нули