add a column to query with difference of time between two date columns.

zn553

I would like based on this query add a column that will display the time elapsed between the 'eventtime' and the 'time_received:

select a.transaction_id, e.eventcode, e.eventtime, a.time_received
from request_history_tbl a 
   join eventlog_tbl e on a.transaction_id = e.transaction_id
   where e.eventtime >= sysdate - 30
   and e.eventcode = '50'
   order by 3 desc;

the time_received and eventtime are of type date.

how can i add a new column per instance 'elapsed that display the diff in seconds per instance between eventtime and time_received?

Solomon Yakobson

Oracle date arithmetic uses day as unit of measure. Therefore:

select  a.transaction_id,
        e.eventcode,
        e.eventtime,
        a.time_received,
        (e.eventtime - a.time_received) * 86400 diff_in_seconds -- 86400 = 24 * 60 * 60
  from      request_history_tbl a 
        join
            eventlog_tbl e
          on a.transaction_id = e.transaction_id
  where e.eventtime >= sysdate - 30
    and e.eventcode = '50'
    order by 3 desc
/

SY.

Frank Kulash

Hi, @zn553

how can i add a new column per instance 'elapsed that display the diff in seconds per instance between eventtime and time_received?

Here's one way:

select a.transaction_id, e.eventcode, e.eventtime, a.time_received
,   (a.time_received - e.eventtime) * 24 * 60 * 60 as elapsed
from request_history_tbl a 
  join eventlog_tbl e on a.transaction_id = e.transaction_id
  where e.eventtime >= sysdate - 30
  and e.eventcode = '50'
  order by 3 desc;

If you'd care to post a little sample data (CREATE TABLE and INSERT statements) and the desired results, then I could test it.

When you subtract one DATE form another, the result is the difference in days (a NUMBER). To get the difference in seconds, multiply the number of days by the number of seconds in a day.

Solomon Yakobson

Oracle date arithmetic uses day as unit of measure. Therefore:

select  a.transaction_id,
        e.eventcode,
        e.eventtime,
        a.time_received,
        (e.eventtime - a.time_received) * 86400 diff_in_seconds -- 86400 = 24 * 60 * 60
  from      request_history_tbl a 
        join
            eventlog_tbl e
          on a.transaction_id = e.transaction_id
  where e.eventtime >= sysdate - 30
    and e.eventcode = '50'
    order by 3 desc
/

SY.

Frank Kulash

Hi, @zn553

how can i add a new column per instance 'elapsed that display the diff in seconds per instance between eventtime and time_received?

Here's one way:

select a.transaction_id, e.eventcode, e.eventtime, a.time_received
,   (a.time_received - e.eventtime) * 24 * 60 * 60 as elapsed
from request_history_tbl a 
  join eventlog_tbl e on a.transaction_id = e.transaction_id
  where e.eventtime >= sysdate - 30
  and e.eventcode = '50'
  order by 3 desc;

If you'd care to post a little sample data (CREATE TABLE and INSERT statements) and the desired results, then I could test it.

When you subtract one DATE form another, the result is the difference in days (a NUMBER). To get the difference in seconds, multiply the number of days by the number of seconds in a day.