Извлечь эпоху из столбца временных меток в Postgres
Каков синтаксис получения эпохи из столбца отметки времени с часовым поясом?
Я могу использовать with time zone следующим образом, и он работает так, как ожидалось
select extract(epoch from timestamp with time zone '1970-01-01 00:00:00.000 +00') ;
возвращается 0
select extract(epoch from timestamp with time zone '1970-01-01 00:00:00.000 -0500') ;
возвращается 18000.000000
Большой!
Но это возвращает одну и ту же эпоху для обоих:
select extract(epoch from ts )
from(
select to_timestamp(ts, 'yyyy-mm-dd h24:mi:ss') ts
from (
select unnest (array[
'1970-01-01 00:00:00.000 +00',
'1970-01-01 00:00:00.000 -0500'
]) ts
)
);
18000.000000
18000.000000
Где мне поставить with time zone, когда я извлекаю эпоху из столбца меток времени?
Я пробовал следующее, но безуспешно:
epoch from ts with time zone--fails Ошибка SQL [42601]: ОШИБКА: синтаксическая ошибка в или рядом с «с»epoch from ts::timestamp with time zone-- работает, но выдает одинаковые значения для обоих
Ответы 3
Возможно, это то, что вы ищете
select extract(epoch from ts)
from(
select ts::TIMESTAMPTZ at time zone 'UTC' ts
from (
select unnest (array[
'1970-01-01 00:00:00.000+00',
'1970-01-01 00:00:00.000-0500'
]) ts
) a
) b;
Из вашего запроса; часовой пояс необходимо определить с помощью приведения, прежде чем использовать эпоху.
at time zone 'UTC'не обязателен.
Да, это действительно не обязательно. Было бы полезно только продемонстрировать, правильно ли отформатирован часовой пояс в этом случае.
При вводе timestamptz на входе в часовом поясе сдвигает его и удаляет из него часовой пояс. ::timestamptz в вашем примере уже демонстрирует правильность смещения, видимого в значениях - независимо от того, удаляете ли вы его или нет, оно правильно выровнено, и один из них заканчивается 0 с эпохи, другой 5h, как в +00 и -0500.
В PostgreSQL при преобразовании текстовой метки времени в метку времени с часовым поясом при преобразовании будет использоваться текущая настройка часового пояса, если только вы явно не укажете часовой пояс при преобразовании.
Однако, поскольку ваши временные метки уже содержат информацию о часовом поясе, вы можете напрямую преобразовать их в timestamptz, не используя to_timestamp:
SELECT extract(epoch FROM ts::timestamptz)
FROM (
SELECT unnest(ARRAY[
'1970-01-01 00:00:00.000 +00',
'1970-01-01 00:00:00.000 -0500'
]) AS ts
) subquery;
Это позволит правильно интерпретировать информацию о часовом поясе, включенную в метки времени, и предоставить правильные значения эпохи.
0.000000
18000.000000
Объяснение:
Первая временная метка «1970-01-01 00:00:00.000 +00» соответствует эпохе 0.
Вторая временная метка «1970-01-01 00:00:00.000 -0500» отстает на 5 часов от UTC, что соответствует эпохе в 18 000 секунд.
С другим форматом, включающим коды форматирования часовых поясов, ваш запрос сработал бы:
to_timestamp(ts, 'YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM')
Это потому, что это
to_timestamp(ts, 'yyyy-mm-dd h24:mi:ss')превращает их в одну и ту же метку времени. Тот, который был скорректирован в соответствии с часовым поясом сервера. Нет необходимости вto_timestampпросто сделай... select ts::timestamptz ts ....