How to get the numeric difference between DBTIMEZONE and SESSIONTIMEZONE?

Question

Is there in Oracle a smooth way to get the numeric difference between SESSIONTIMEZONE and DBTIMEZONE at the current moment (when I perform the call)?

For instance:

SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

Returns:

+04:00  +07:00

So, i need some kind of function, by calling which with given parameters, I get the difference between these two values.

For the instance above:

SELECT get_numeric_offset(SESSIONTIMEZONE, DBTIMEZONE) FROM DUAL;

Would return -3 (the sign is crucial).

Of course, it's possible for me to write this function myself by working with strings and parsing them and then proceeding some arithmetical operations or do something like this (which I still don't consider as a pretty smooth solution:

SELECT (
        CAST(SYSTIMESTAMP AT TIME ZONE SESSIONTIMEZONE AS DATE) - 
        CAST(SYSTIMESTAMP AT TIME ZONE DBTIMEZONE AS DATE)
       )*24 
 FROM DUAL;

Maybe I missed something and Oracle actually provides a way to calculate difference between two given timezones?

Answer 1

A little warning on using SESSIONTIMEZONE

Your first example is not bullet-proof, as the session time zone can be set to either of the following :

• Operating system local time zone ('OS_TZ')
• Database time zone ('DB_TZ')
• Absolute offset from UTC (for example, '-05:00')
• Time zone region name (for example, 'Europe/London')

Just look at what happens here :

SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE  |  DBTIMEZONE
         +04:00  |      +07:00

ALTER SESSION SET TIME_ZONE='Europe/Paris';

SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE  |  DBTIMEZONE
   Europe/Paris  |      +07:00

Parsing that 'Europe/Paris' string in your function is going to be a lot harder... You should use the TZ_OFFSET function, to ensure that you always get the same ±HH:MM format.

ALTER SESSION SET TIME_ZONE='Europe/Paris';

SELECT DBTIMEZONE, SESSIONTIMEZONE, TZ_OFFSET(SESSIONTIMEZONE) FROM DUAL;

 DBTIMEZONE | SESSIONTIMEZONE | TZ_OFFSET(SESSIONTIMEZONE)
     +07:00 |    Europe/Paris |                     +02:00

About your second solution

I think I like your second solution better. You can shorten it by using CURRENT_DATE instead of CAST(SYSTIMESTAMP AT TIME ZONE SESSIONTIMEZONE AS DATE), they are equivalent :

SELECT (
        CURRENT_DATE - 
        CAST(SYSTIMESTAMP AT TIME ZONE DBTIMEZONE AS DATE)
       )*24 
 FROM DUAL;

You could technically even do this !

SELECT (CURRENT_DATE - SYSDATE) * 24 
 FROM DUAL;

BUT actually, SYSDATE is not guaranteed to be on the same timezone as DBTIMEZONE. SYSDATE is always bound to the underlying OS' timezone, while DBTIMEZONE can be altered once the server has been started.

And while I'm splitting hairs, I should also remind you that certain countries / regions use offsets that are not whole numbers, for example Iran Standard Time is UTC+03:30, Myanmar Time is UTC+06:30... I don't know if you'll ever encounter this in your production environment, but I hope you're okay with the possibility that your query returns something like 1.5...