Hi All,
I am trying to get time difference between two named time-zones.
for e.g. if tz1 Asia/Kolkata (UTC offset +05:30) and tz2 is Europe/Berlin (UTC offset +01:00), then tz1 - tz2 should give +04:30 (Kolkata is 4 hours 30 minutes ahead of Berlin, as on today)
Similarly if tz1 US/Eastern (UTC offset -04:00) and tz2 is Europe/Berlin (UTC offset +01:00), then tz1 - tz2 should give -05:00 (US/Eastern time is 5 hours behind of Berlin, as on today)
I can use TZ_OFFSET function to get UTC offset of named timezone. Next, I am thinking of calculating time difference by extracting hours and minutes from time zone offset and doing the math, like below..
ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,';
-- only works for below timezones, coding pending for other timezones (other cases)
SELECT
to_number(replace(tz_offset('US/Eastern'),':','.')) - to_number(replace(TZ_OFFSET( 'Europe/London' ),':','.')) ff
FROM
DUAL;
But, before proceeding further, I want to know if there is any better way to achieve this? or is there any inbuilt function which can give desired output? Thanks in advance for your help.
DB: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Regards,
Hari