This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Oct 6, 2012 10:20 AM by 966581 RSS

Some quick assistance - PLSQL Query Question

966581 Newbie
Currently Being Moderated
I am trying to write a SQL statement that takes a timestamp value from one field of code and subtracts it from another timestamp. I need to find a way to identify the one field from the other in the equation.

TASK AHT START_TIMESTAMP
POI 40 10/1/2012 6:04:25
TRANS 59 10/1/2012 6:04:44

So, in the example above, I want to write a SQL which suggests subtracting "TRANS" timestamp from "POI" timestamp to get a value in seconds.

Can someone suggest how to write this SQL to perform this task?

Thanks.
  • 1. Re: Some quick assistance - PLSQL Query Question
    812975 Explorer
    Currently Being Moderated
    with xx as (
    SELECT 'POI' TASK, 40 AHT, TO_DATE('10/1/2012 6:04:25', 'MM/DD/YYYY HH24:MI:SS') START_TIMESTAMP FROM DUAL UNION ALL
    SELECT 'TRANS' TASK, 59 AHT, TO_DATE('10/1/2012 6:04:44', 'MM/DD/YYYY HH24:MI:SS') START_TIMESTAMP FROM DUAL
    )
    SELECT ROUND((MAX(CASE WHEN TASK = 'TRANS' THEN START_TIMESTAMP END) - MAX(CASE WHEN TASK = 'POI' THEN START_TIMESTAMP END))*24*60*60) AS SEC
    FROM XX;
  • 2. Re: Some quick assistance - PLSQL Query Question
    ranit B Expert
    Currently Being Moderated
    try this...
    with xx as(
      select 'POI' as TASK,40 as AHT,'10/1/2012 6:04:25' as start_ts from dual union all
      select 'TRANS' as TASK,59 as AHT,'10/1/2012 6:04:44' as start_ts from dual
    )  
    SELECT extract(second FROM 
      ((select to_timestamp(a.start_ts,'dd/mm/yyyy hh:mi:ss') from xx a where a.task = 'TRANS')-
      ((select to_timestamp(a.start_ts,'dd/mm/yyyy hh:mi:ss') from xx a where a.task = 'POI'))) 
    ) as secos FROM dual;
    gives
    19
    HTH.
    Ranit B.
  • 3. Re: Some quick assistance - PLSQL Query Question
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    Oracle DATEs are much easier to manipulate than TIMESTAMPs. If you can ignore the fractions of a second, then you can do something like:
    SELECT     ( MIN (CASE WHEN task = 'POI'   THEN CAST (start_timestamp AS date) END)
               - MIN (CASE WHEN task = 'TRANS' THEN CAST (start_timestamp AS date) END)
         ) * 24 * 60 * 60     AS trans_poi_seconds
    FROM     table_x
    ;
    When you subtract one DATE from another, the result is a NUMBER, the difference in days. To get the difference in seconds, multiply the number of days by the number of seconds in a day.

    Instead of MIN (CASE ...), you can do a self-join.
  • 4. Re: Some quick assistance - PLSQL Query Question
    ranit B Expert
    Currently Being Moderated
    SaadL wrote:
    with xx as (
    SELECT 'POI' TASK, 40 AHT, TO_DATE('10/1/2012 6:04:25', 'MM/DD/YYYY HH24:MI:SS') START_TIMESTAMP FROM DUAL UNION ALL
    SELECT 'TRANS' TASK, 59 AHT, TO_DATE('10/1/2012 6:04:44', 'MM/DD/YYYY HH24:MI:SS') START_TIMESTAMP FROM DUAL
    )
    SELECT ROUND((MAX(CASE WHEN TASK = 'TRANS' THEN START_TIMESTAMP END) - MAX(CASE WHEN TASK = 'POI' THEN START_TIMESTAMP END))*24*60*60) AS SEC
    FROM XX;
    I guess CASE statement twice along with ROUND and MAX will be tooooo costly.
  • 5. Re: Some quick assistance - PLSQL Query Question
    ranit B Expert
    Currently Being Moderated
    Frank Kulash wrote:

    Oracle DATEs are much easier to manipulate than TIMESTAMPs. If you can ignore the fractions of a second, then you can do something like:
    SELECT     ( MIN (CASE WHEN task = 'POI'   THEN CAST (start_timestamp AS date) END)
         - MIN (CASE WHEN task = 'TRANS' THEN CAST (start_timestamp AS date) END)
         ) * 24 * 60 * 60     AS trans_poi_seconds
    FROM     table_x
    ;
    Don't you think cost priority is more than a minor manipulation effort?
  • 6. Re: Some quick assistance - PLSQL Query Question
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    ranit B wrote:
    Don't you think cost priority is more than a minor manipulation effort?
    Sure, sometimes there's a tradeoff between speed and simplicity. Regular expressions are a common example.

    In this case, is there such a tradeoff? I don't think so, though, of course, what someone considers simple and easy to maintaIn is rather subjective. I find CAST easier to uderstand than TO_TIMESTAMP and TO_CHAR, and I think other people do, too. My guess is that CAST is more efficient than either TO_TIMESTAMP or TO_CHAR, let alone both of them together. But don't rely on my guess; try it and see.

    Edited by: Frank Kulash on Oct 6, 2012 10:19 AM
  • 7. Re: Some quick assistance - PLSQL Query Question
    ranit B Expert
    Currently Being Moderated
    Frank Kulash wrote:
    Hi,
    ranit B wrote:
    Don't you think cost priority is more than a minor manipulation effort?
    No. My guess is that CAST is more efficient than either TO_TIMESTAMP or TO_CHAR, let alone both of them together. But don't rely on my guess; try it and see.
    1. Efficient?? In terms of what?
    2. What about the CASE WHEN clause? This is somewhat close to a Pl/Sql's If-Else... don't you think now it's getting way to costly?

    Please rectify me if i'm wrong.

    Ranit B.
  • 8. Re: Some quick assistance - PLSQL Query Question
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    ranit B wrote:
    1. Efficient?? In terms of what?
    Execution time.
    2. What about the CASE WHEN clause? This is somewhat close to a Pl/Sql's If-Else... don't you think now it's getting way to costly?
    No. Using CASE, you can get both values with a single table scan. Otherwise, you can need two separate sub-queries to get the two different values. If the problem is really as simple as OP posted it, then those 2 separate queries can be really fast, but if the problem is that simple, it will be difficult to notice a difference in speed whatever you do.
  • 9. Re: Some quick assistance - PLSQL Query Question
    ranit B Expert
    Currently Being Moderated
    ok. Then cool... But i still prefer my query. ;-)

    Frank,
    I'm your big fan and love to learn new stuff whatever you share in this forum.

    Thanks for nourishing our grey cells.
    Ranit B.
  • 10. Re: Some quick assistance - PLSQL Query Question
    966581 Newbie
    Currently Being Moderated
    Ok. Maybe this will help us further.....

    In my select SQL statement, I use the following:

    to_char(create_date,'sssss') - to_char(start_date,'sssss') "GREETING_AHT",
    to_char(to_char(end_date,'sssss') - to_char(last_updated_date,'sssss')) "CLOSING_AHT",
    create_date "CREATE_DATE",
    last_updated_date "UPDATED_DATE"

    The results from the SQL produce the following:

    VALUE_NAME TOTAL_TALK_TIME     GREETING_AH CLOSING_AHT     CREATE_DATE
    POI Search     69     40     4     10/1/2012 6:04:25 AM
    Transmit Route     69     59     4     10/1/2012 6:04:44 AM

    All I need now is to write a quick SQL statement which takes the "CREATE_DATE" from "Value_Name" = 'POI_SEARCH' and subtracts this timestamp from "Value_Name" = 'TRANSMIT Route' - using the to_char(create_date,'sssss') definition.

    sorry for any confusion.

    Can someone assist me?
  • 11. Re: Some quick assistance - PLSQL Query Question
    ranit B Expert
    Currently Being Moderated
    why don't you try the query which i gave above... i guess it'll meet your requirement.

    If not, please let me know why. I'll fix.
    Ranit B.
  • 12. Re: Some quick assistance - PLSQL Query Question
    966581 Newbie
    Currently Being Moderated
    Not sure how to enter it within the select statement
  • 13. Re: Some quick assistance - PLSQL Query Question
    ranit B Expert
    Currently Being Moderated
    with xx as(
      select 'POI' as value_name,40 as AHT,'10/1/2012 6:04:25' as create_date from dual union all
      select 'TRANS' as value_name,59 as AHT,'10/1/2012 6:04:44' as create_date from dual
    )  
    this is nothing, just assume it like an inline view(or your dummy table data).
    Google it - WITH clause (also called Subquery Factoring )
    SELECT extract(second FROM 
      ((select to_timestamp(a.create_date,'dd/mm/yyyy hh:mi:ss') from xx a where a.value_name= 'TRANS')-
      ((select to_timestamp(a.create_date,'dd/mm/yyyy hh:mi:ss') from xx a where a.value_name = 'POI'))) 
    ) as secos FROM dual;
    XX - is your actual table.

    Hope you understand now.

    Ranit B.
  • 14. Re: Some quick assistance - PLSQL Query Question
    966581 Newbie
    Currently Being Moderated
    In your explanation below, why couldn't I use;

    (case when task = 'POI' then CAST (start_timestamp as date1)
    when task = 'TRANS' then CAST (start_timestamp as date2)
    then 'date1' - 'date2' as 'POI_AHT' else 'NULL_AHT' end,


    Is this possible?

    I just want the value of the POI start_timestamp less TRANS start-timestamp

    Thanks.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points