Блог

Главная - Вопросы по программированию - Запрос базы данных Oracle с различными часовыми поясами

Запрос базы данных Oracle с различными часовыми поясами

#sql #oracle #time

Вопрос:

У меня есть виртуальная машина со скриптом Python, которая подключается к базе данных Oracle через cx_Oracle модуль.

Проблема в том, что виртуальная машина находится в часовом поясе UTC, но база данных находится в часовом поясе Европа/Лиссабон, поэтому после изменения летнего времени они не выровнены:

 select current_timestamp from dual; --21.04.14 10:26:34,902780  00:00
select systimestamp from dual; --21.04.14 11:27:04,542267  01:00

Я выполняю запрос к таблице со столбцом типа ДАТА, называемым CREATION_DATE :

 SELECT
    count(*)
FROM
    MY_TABLE
WHERE
    CREATION_DATE >= TO_DATE('13/04/2021 14:00:00', 'DD/MM/YYYY HH24:MI:SS') 
AND 
    CREATION_DATE < TO_DATE('13/04/2021 15:00:00', 'DD/MM/YYYY HH24:MI:SS');

Эти значения дат заполняются по местному времени виртуальной машины, которое указано в UTC. В таблице есть некоторые записи CREATION_DATE в этом диапазоне, но в португальском часовом поясе ( 1 летом), поэтому они не извлекаются с помощью этого запроса:

 CREATION_DATE
-------------------
13/04/2021 15:20:47
13/04/2021 15:20:47
13/04/2021 15:20:47
13/04/2021 15:20:47

Этот запрос работал до тех пор, пока не изменилось летнее время. Как я могу перестроить запрос, чтобы эти записи были правильно извлечены независимо от времени года?

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

1. Каков тип данных столбцов CREATION_DATE — включает ли он часовой пояс? Кроме того, кажется, что часовые пояса в любом случае ни на что в вашем запросе/данных не влияют — ваш запрос не вернет никаких записей, потому что ни у одной записи нет даты/времени в диапазоне, который вы жестко закодировали в предложении WHERE. Я мог бы понять, что существует возможная проблема, если бы вы использовали, например, такие функции, как CURRENT_DATE

2. Этот столбец относится к типу ДАТЫ и не включает часовой пояс. Я не могу использовать эти функции, потому что эта дата на самом деле не жестко задана, я получаю ее из скрипта Python, который запускает запрос.

3. если вы используете тип данных ДАТЫ, то база данных или кто-либо другой, если на то пошло, не может сказать, что из-за изменения летнего времени вам необходимо скорректировать часовую часть. Все ли даты в CREATION_DATE находятся в одном часовом поясе?

Ответ №1:

Я пришел к решению. Поскольку я хочу, чтобы виртуальная машина всегда находилась в часовом поясе UTC, и я также не хочу менять часовой пояс базы данных, я могу использовать это:

 SELECT CAST(
    TO_TIMESTAMP_TZ(
        '13/04/2021 14:00:00 UTC',
        'DD/MM/YYYY HH24:MI:SS TZR'
    ) AT TIME ZONE 'Europe/Lisbon' AS DATE
) FROM DUAL;

Который вернет значение, преобразованное из часового пояса UTC в часовой пояс Европы/Лиссабона:

 13/04/2021 15:00:00

Применяя это к моему запросу, это было бы так:

 SELECT
    count(*)
FROM
    MY_TABLE
WHERE
    CREATION_DATE >= CAST(
        TO_TIMESTAMP_TZ(
            '13/04/2021 14:00:00 UTC',
            'DD/MM/YYYY HH24:MI:SS TZR'
        ) AT TIME ZONE 'Europe/Lisbon' AS DATE
    )
AND 
    CREATION_DATE < CAST(
        TO_TIMESTAMP_TZ(
            '13/04/2021 15:00:00 UTC',
            'DD/MM/YYYY HH24:MI:SS TZR'
        ) AT TIME ZONE 'Europe/Lisbon' AS DATE
    );

И я получаю желаемые результаты:

   COUNT(*)
----------
         4

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

1. Поскольку вы жестко кодируете дату/время в предложении WHERE, не было бы проще преобразовать в UTC в вашей голове (т. Е. Добавить/вычесть 1 час) и указать соответствующее время в предложении WHERE?

Метки: