create table test_tz(
col1 varchar2(10),
col2 timestamp);
insert into test_tz select dbms_random.string('A','10'), systimestamp at time zone 'Europe/London' from dual;
select * from test_tz;
| COL1 | COL2 |
|---|
| OlvUrITHKp | 01-APR-19 05.59.57.644499 PM |
| PyHHhJvHrG | 01-APR-19 06.00.02.327258 PM |
| cSoHaaEyzr | 01-APR-19 05.59.51.722169 PM |
| msIjcgAGbM | 01-APR-19 05.59.53.411279 PM |
when i try to find difference of time like this
select systimestamp at time zone dbtimezone tz , col2, systimestamp at time zone dbtimezone - col2 diff from test_tz;
i am getting time difference of 7 hours. as you can see below in the column DIFF which is wrong, now i tried to extract hour, day and minutes also, even that did not work.
| TZ | COL2 | DIFF |
|---|
| 01-APR-19 05.03.11.654567 PM +00:00 | 01-APR-19 05.59.57.644499 PM | -000000000 07:56:45.989932 |
| 01-APR-19 05.03.11.654567 PM +00:00 | 01-APR-19 06.00.02.327258 PM | -000000000 07:56:50.672691 |
| 01-APR-19 05.03.11.654567 PM +00:00 | 01-APR-19 05.59.51.722169 PM | -000000000 07:56:40.067602 |
| 01-APR-19 05.03.11.654567 PM +00:00 | 01-APR-19 05.59.53.411279 PM | -000000000 07:56:41.756712 |
how can i find the time difference between 2 different time zones, can anyone please help ?