Forum Stats

  • 3,768,299 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

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

zn553
zn553 Member Posts: 8 Green Ribbon

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?

Tagged:

Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond
    edited Jun 2, 2021 2:18PM Accepted Answer

    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
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    Accepted Answer

    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.

    zn553

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond
    edited Jun 2, 2021 2:18PM Accepted Answer

    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
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    Accepted Answer

    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.

    zn553