0

I have a VM with a Python script that connects to an Oracle Database through the cx_Oracle module.

The issue is that the VM is in the UTC time zone but the database is in the Europe/Lisbon time zone, so, after the summer time change, they are not aligned:

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

I am doing a query against a table with a column of type DATE, called 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');

Those date values are filled from the VM local time, which is in UTC. The table have some records with CREATION_DATE in that range, but in Portuguese time zone (+1 in summer), so they are not retrieved with this query:

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

This query worked until the summer time change. How can I rebuild the query so those records are correctly retrieved, independently of the time of the year?

Juan Manuel Triguero Piñero
  • 31
  • 4
  • What is the datatype of the CREATION_DATE columns - does it include timezone? Also, nothing in your query/data seems to be affected by timezones anyway - your query won't return any records because no records have a date/time in the range you have hardcoded in your WHERE clause. I could understand there being a possible issue if you were using, for example, functions like CURRENT_DATE – NickW Apr 14 '21 at 11:00
  • That column is DATE type, and does not include time zone. I cannot use those functions because that date is not actually hardcoded, I get it from the Python script that runs the query. – Juan Manuel Triguero Piñero Apr 14 '21 at 11:05
  • if you're using a DATE data type, then the database, or anyone else for that matter, cannot tell that due to summer time change, you have to adjust the hour part. Are all the dates in CREATION_DATE in the same timezone? – gsalem Apr 14 '21 at 11:35

1 Answers1

1

I have come to a solution. As I want the VM to always be in UTC time zone, and I don't want to change the database time zone either, I can use this:

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;

Which will return the value converted from the UTC time zone to the Europe/Lisbon time zone:

13/04/2021 15:00:00

Applying this to my query, it would be like this:

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

And I get the desired results:

  COUNT(*)
----------
         4
Juan Manuel Triguero Piñero
  • 31
  • 4
  • As you are hardcoding the date/time in your WHERE clause, wouldn't it be simpler to convert to UTC in your head (i.e. add/subtract 1 hour) and put the appropriate time in your WHERE clause? – NickW Apr 14 '21 at 16:24