Oracle 19c, Timestamp, Timezone [closed]

Question

The result of the following statement does not appear to be accurate or correct.

select(to_timestamp_tz('2025-10-02 08:24:00', 'yyyy-mm-dd hh24:mi:ss')at time zone 'Europe/Berlin')-(to_timestamp_tz('2025-10-02 18:50:00', 'yyyy-mm-dd hh24:mi:ss'
)at time zone 'America/New_York')as time_needed
from dual;

It should be somewhere around 8 hours.

Answer 1

Answering the first version of this question:

Use TIMESTAMP literals:

select TIMESTAMP '2025-10-02 08:24:00' AT LOCAL
       - TIMESTAMP '2025-10-02 18:50:00 America/New_York' as time_needed
from   dual;

or FROM_TZ:

select TIMESTAMP '2025-10-02 08:24:00' AT LOCAL
       - FROM_TZ(TIMESTAMP '2025-10-02 18:50:00', 'America/New_York') as time_needed
from   dual;

---

If you do:

ALTER SESSION SET TIME_ZONE='Europe/Paris';

Then the output of the above queries is:

TIME_NEEDED
-000000000 16:26:00.000000000

If you do:

ALTER SESSION SET TIME_ZONE='America/Los_Angeles';

Then the output of the above queries is:

TIME_NEEDED
-000000000 07:26:00.000000000

And, if you do:

ALTER SESSION SET TIME_ZONE='America/Chicago';

Then the output of the above queries is:

TIME_NEEDED
-000000000 09:26:00.000000000

fiddle

---

Regarding your updated question, if you convert the times to UTC:

SELECT TIMESTAMP '2025-10-02 08:24:00 Europe/Berlin' AT TIME ZONE 'UTC' AS time1,
       TIMESTAMP '2025-10-02 18:50:00 America/New_York' AT TIME ZONE 'UTC' AS time2,
       TIMESTAMP '2025-10-02 08:24:00 Europe/Berlin' - TIMESTAMP '2025-10-02 18:50:00 America/New_York' AS diff
from   dual;

TIME1	TIME2	DIFF
2025-10-02 06:24:00.000000000 UTC	2025-10-02 22:50:00.000000000 UTC	-000000000 16:26:00.000000000

Then you can see that there is about 16 hours between the two times, not 8.

The query is accurate.

fiddle

Answer 2

The problem of your query is this:

to_timestamp_tz requires a string with time zone information. But you provide just '2025-10-02 08:24:00' so Oracle has to guess the timezone. Well, Oracle does not guess, instead it takes your SESSIONTIMEZONE as default.

When you use to_timestamp_tz('2025-10-02 18:50:00', 'yyyy-mm-dd hh24:mi:ss') at time zone 'America/New_York' then Oracle runs in principle to_timestamp_tz('2025-10-02 18:50:00 Europe/Berlin 'yyyy-mm-dd hh24:mi:ss tzr') at time zone 'America/New_York' (assuming your SESSIONTIMEZONE is 'Europe/Berlin'). I don't think that is what you like to do.

Note, when you use timestamp with time zone values, then time differences are always based on UTC time, so time zone shifting is not needed.