It was helpful, but one more question
how can I get the difference from different days as well?
Below is giving 0 but I need it as 24

select abs(extract(hour from diff)) hrs from (
select 
  to_timestamp_tz(  '28-JAN-2016 10:30:00.000000 PM +00:00', 'dd-mon-yyyy hh:mi:ssxff PM tzh:tzm') -
  to_timestamp_tz(   '29-JAN-2016 10:30:00.000000 PM +00:00', 'dd-mon-yyyy hh:mi:ssxff PM tzh:tzm') diff
from dual
);
   

SQL> select abs(24*extract(day from diff)+extract(hour from diff)) hrs from (
  2  select
  3    to_timestamp_tz(  '28-JAN-2016 10:30:00.000000 PM +00:00', 'dd-mon-yyyy hh:mi:ssxff PM tzh:tzm') -
  4    to_timestamp_tz(   '29-JAN-2016 10:30:00.000000 PM +00:00', 'dd-mon-yyyy hh:mi:ssxff PM tzh:tzm') diff
  5  from dual
  6  );

       HRS
----------
        24

Hi,

I just fixed it by converting into mins.. which serves my purpose

             
    select abs (extract( day from diff ))*24*60 +
           abs(extract( hour from diff ))*60 +
           abs(extract( minute from diff ))  total_mins
      from (select 
  to_timestamp_tz(      '14-Feb-17 07.00.00.000000000 PM +00:00'   , 'dd-mon-yyyy hh:mi:ssxff PM tzh:tzm') -
     to_timestamp_tz(    '15-Feb-17 07.00.00.000000000 PM +00:00'  , 'dd-mon-yyyy hh:mi:ssxff PM tzh:tzm')  diff
             from dual)

See, when we subtract two timestamps the result is a INTERVAL DAY, giving to us the number of DAYS, HOURS, MINUTES, such as :

SQL> select systimestamp, systimestamp - to_timestamp('12/02/17 16:53:55.092', 'dd/mm/rr HH24:MI:SS.FF3') result from dual;

SYSTIMESTAMP RESULT
--------------------------------- -----------------------------
14/02/17 18:06:40,794000 -02:00 +000000002 01:12:45.702000000

==> SOOO, if we ask for a EXTRACT of Days, we will get two days (this was the quantity of days on the result, see above - AND working with my timezone, here : as said if you want to use a given TZ indicate it), if we ask for a EXTRACT oh hours , we will get on my example One hour (this was the quantity of hours in the result) :

SQL> select extract (DAY from DIFF) Days,
2 extract (HOUR from DIFF) Hours,
3 extract( MINUTE from DIFF) MInutes,
4 extract (SECOND from DIFF) Seconds
5 from (select systimestamp - to_timestamp('12/02/17 16:53:55.092', 'dd/mm/rr HH24:MI:SS.FF3') DIFF from dual);

DAYS HOURS MINUTES SECONDS
---------- ---------- ---------- ----------
2 1 18 58,837

SQL>

==> You WILL NOT receive 24 hours if if inform yesterday, the result is in the form of DAYS, HOURS, MINUTES and fractional SECONDS, right ?? In your example, you get 1 day, 0 hours, 0 minutes and 0 seconds as the difference, so EXTRACT HOUR is returning zero, RXTRACT DAY would give 1... Working as expectec...

To get the total number of Hours, you would need to CONVERT the quantity of days to Hours and add it with the other components, more or less like :

SQL> select extract (DAY from DIFF)*24 A,
2 extract (HOUR from DIFF) B,
3 extract( MINUTE from DIFF) / 60 C,
4 extract (SECOND from DIFF) / 60 / 60 D
5* from (select systimestamp - to_timestamp('12/02/17 16:53:55.092', 'dd/mm/rr HH24:MI:SS.FF3') DIFF from dual);

A B C D
---------- ---------- ---------- ----------
48 1 ,466666667 ,002921944

SQL>

In my example I would add the 48 hours (of the two days interval) with the 1 whole hour and (if needed) the rest of the factional hour informations....

Regards,

J. Laurindo Chiappa

How to create a counter in HRS between "now" and last update.

I have a transaction table t with a statusdate column of type date. I would like to create a diff column in hours so have been trying it between systimestamp and the statusdate column cast as timestamp. It is not clear to me from the results whether I am accounting for the question of timezone properly. I see the -5:00 for systimestamp, but not sure about the conversion of the statusdate. Finally, how to get the diff in hrs?

/** find diff between now and statusdate, hrs, in table t **/
select txn_num, systimestamp, cast(statusdate as timestamp) statusdate, systimestamp-statusdate diff from t;

txn_num | systimestamp | statusdate | diff
2261|2018-07-06 08:03:18.837 -5:00|2018-06-02 15:55:09.0|33 17:8:9.837

select extract ( 
         hour from systimestamp - cast ( date'2018-07-01' as timestamp ) 
       ) hr, 
       extract ( 
         day from systimestamp - cast ( date'2018-07-01' as timestamp ) 
       ) dys,
       extract ( 
         hour from systimestamp - cast ( date'2018-07-01' as timestamp ) 
       ) + 24 * extract ( 
         day from systimestamp - cast ( date'2018-07-01' as timestamp ) 
       ) total_hrs
from   dual;

HR   DYS   TOTAL_HRS   
  11     8         203 

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 systimestamp at time zone dbtimezone - col2 diff from test_tz;

it is returning wrong result

SQL> create table test_tz(
  2  col1 varchar2(10),
  3  col2 timestamp with time zone);

Table created.

SQL>
SQL> insert into test_tz select dbms_random.string('A','10'), systimestamp at time zone 'Europe/London' from dual;

1 row created.

SQL>
SQL> select col2, systimestamp at time zone dbtimezone, systimestamp at time zone dbtimezone - col2 diff from test_tz;

COL2
---------------------------------------------------------------------------
SYSTIMESTAMPATTIMEZONEDBTIMEZONE
---------------------------------------------------------------------------
DIFF
---------------------------------------------------------------------------
02-APR-19 07.44.08.474000 AM EUROPE/LONDON
02-APR-19 06.44.08.481000 AM +00:00
+000000000 00:00:00.007000