How to show real time difference between timestamps in two different zones with SQL?-CodePudding

This query returns 0

SELECT (CURRENT_TIMESTAMP AT TIME ZONE 'PST' 
      - CURRENT_TIMESTAMP AT TIME ZONE 'UTC') AS td
  FROM dual

How can I make it to actually show the real difference in time? For example, in this case I want to see a difference of -8 hours.

In this example I used CURRENT_TIMESTAMP, but I have a real use case where I have timestamps in two different time zones. And I want the real time difference between those two.

CodePudding user response：

Cast the values to a TIMESTAMP without a time zone:

SELECT CAST(CURRENT_TIMESTAMP AT TIME ZONE 'PST' AS TIMESTAMP)
       - CAST(CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AS TIMESTAMP)
       as td
FROM   DUAL;

Which outputs:

TD

-000000000 08:00:00.000000

TD
-000000000 08:00:00.000000

db<>fiddle here

CodePudding user response：

So, you want to find the time difference between the time zones. (This is not what the title says; the title is misleading.)

If so, then you don't need to reference current_timestamp, or anything of the kind.

Since you are comparing PST to UTC, this is the same as finding the UTC offset of PST. This makes the problem even easier. (In the general case, you can find the offset of both time zones and subtract; in your example, the offset of UTC to itself is zero, obviously).

select to_char(to_timestamp_tz('PST', 'tzr'), 'tzh:tzm') as pst_offset
from   dual;


PST_OFFSET
----------
-08:00