CODEWITHSUNDEEP
sqloracle-databasetimezonetimezone-offset
MetallicPriest
MetallicPriest

Reputation: 30815

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

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.

Upvotes: 0

Views: 644

Answers (2)

MT0
MT0

Reputation: 168416

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

Or, considering times around the DST boundary:

-- Times around the DST boundary.
WITH times (t) AS (
  SELECT TIMESTAMP '2021-03-14 09:30:00 UTC' FROM DUAL UNION ALL
  SELECT TIMESTAMP '2021-03-14 10:30:00 UTC' FROM DUAL
)
SELECT t As t_utc,
       t AT TIME ZONE 'PST8PDT' AS t_pstpdt,
       (CAST(t AT TIME ZONE 'PST8PDT' AS TIMESTAMP)
        - CAST(t AT TIME ZONE 'UTC' AS TIMESTAMP)) AS td
FROM   times

Outputs:

T_UTC T_PSTPDT TD
2021-03-14 09:30:00.000000000 UTC 2021-03-14 01:30:00.000000000 PST8PDT -000000000 08:00:00.000000
2021-03-14 10:30:00.000000000 UTC 2021-03-14 03:30:00.000000000 PST8PDT -000000000 07:00:00.000000

db<>fiddle here

Upvotes: 3

user5683823
user5683823

Reputation:

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

Upvotes: 0

Related Questions