CODEWITHSUNDEEP
sqloracle-databasetime
Juan Manuel Triguero Piñero
Juan Manuel Triguero Piñero

Reputation: 31

Oracle DB query with different time zones

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?

Upvotes: 0

Views: 657

Answers (1)

Juan Manuel Triguero Pi&#241;ero
Juan Manuel Triguero Pi&#241;ero

Reputation: 31

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

Upvotes: 1

Related Questions