Forum Stats

  • 3,782,963 Users
  • 2,254,716 Discussions
  • 7,880,231 Comments

Discussions

Categories

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

zn553
zn553 Member Posts: 8 Green Ribbon
in SQL & PL/SQL

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:
· Share on TwitterShare on Facebook

Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,018 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.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,407 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
    · Share on TwitterShare on Facebook

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,018 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.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,407 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
    · Share on TwitterShare on Facebook