Удалите UTC из поля МЕТКИ ВРЕМЕНИ в SQL(BigQuery)

#sql #datetime #timestamp

Вопрос:

Я смог преобразовать поле МЕТКИ ВРЕМЕНИ, которое находится в формате UTC, в CST, используя:

 SELECT  
    TIMESTAMP(started_at) AS UTC,  
    TIMESTAMP_SUB(TIMESTAMP (started_at), INTERVAL 5 HOUR) AS CST

Это возвращает:

ряд	UTC	CST
1	2020-05-17 13:07:22 UTC	2020-05-17 08:07:22 UTC

Вторая МЕТКА времени отображает правильную дату и время, но UTC все еще отображается. Каков самый простой способ заменить » UTC » на » CST «или, в качестве альтернативы, вообще удалить «UTC», поскольку мне не нужно обозначение в самом поле?

Ответ №1:

Вы можете использовать встроенную функцию простой замены sql

 select replace(TIMESTAMP_SUB(TIMESTAMP (started_at), INTERVAL 5 HOUR), 'UTC','') AS CST

Вышеизложенное выполняет поиск результата вашего выражения (указанного в вашем вопросе) 'UTC' и заменяет его ничем '' . Вы также можете заменить его, 'CST' как вы отметили.

Обязательное предупреждение: Я полагаю, что ваш запрос будет правильным только в течение полугода, пока соблюдается летнее время? Возможно, вы захотите изучить sys.time_zone_info и его ссылку на MSDN.

1. Спасибо вам за помощь. Я понимаю, что это будет работать только в то время, когда в США действует стандартное время, но тематическое исследование проекта capstone, над которым я работаю, действительно больше сосредоточено на продолжительности аренды велосипедов и с кивком в сторону отслеживания аренды по дням недели. Однако я обязательно отмечу, что преобразование в DST не было выполнено, и буду иметь это в виду для следующего «реального» анализа.:)

2. Теперь я получаю эту ошибку при вставке кода: Нет совпадающей подписи для замены функции для типов аргументов: МЕТКА ВРЕМЕНИ, СТРОКА, СТРОКА. Поддерживаемые подписи: ЗАМЕНИТЬ(СТРОКА, СТРОКА, СТРОКА); ЗАМЕНИТЬ(БАЙТЫ, БАЙТЫ, БАЙТЫ) в [2:5]. Что я упускаю?

Ответ №2:

Мой совет-преобразовать метку времени в datetime локальное значение:

 select datetime(started_at, 'America/Chicago') as started_at_cst

Обратите внимание, что часовой пояс не сохраняется в значении данных. Вместо этого это кодирует значение в строке.

Если вы хотите включить значение часового пояса, я обнаружил, что лучший подход-использовать строку (аргххх!). Следующие конструкции представляют собой строку:

  format_timestamp('%F %X%z', started_at, 'America/Chicago') as started_at_str

который может быть легко преобразован в метку времени для вычисления даты/времени:

 timestamp(started_at_str)

1. ОК. Для этого не нужно указывать значение часового пояса. Я просто хочу иметь возможность рассчитать правильный день недели в правильном часовом поясе и не отвлекаться, показывая неправильную метку часового пояса, даже если МЕТКА ВРЕМЕНИ была обновлена. Ценю ваш ответ.

2. @KevinKish . . . Для s нет «неправильного» часового TIMETSTAMP пояса. Они всегда представлены в UTC. Если вы сохраните значение в виде строк, то сможете обойти эту проблему.

3. Ааа. Так что, вероятно, это объясняет, почему я не смог найти решение там, где искал его … пытаясь преобразовать саму МЕТКУ ВРЕМЕНИ. Действительно приятно знать это сейчас. Надо пойти положить немного льда на голову, где я стучал им по столу 😉

4. @KevinKish . . . Я описал решение, которое я придумал.