Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do i perform a calculation based on a specific time of the day in Oracle?

Posted on 2012-03-31
2
Medium Priority
?
461 Views
Last Modified: 2012-03-31
I would like to calculate the minutes in Oracle if the time passes 1530. I am having trouble with the best way to hard code time time and cacalulate

Thanks so much!

 CASE WHEN
       TO_CHAR(ptoutor.tracking_time_in  ,'hh24mi') > '1530'
    THEN
      (15:30?? - ptinor.tracking_time_in ) * 1440
    else
     (ptoutor.tracking_time_in - ptinor.tracking_time_in  ) * 1440  
    END
0
Comment
Question by:MIREESE
2 Comments
 
LVL 21

Accepted Solution

by:
flow01 earned 2000 total points
ID: 37791853
with tr
    as
    (select to_date('31/03/2012 14:00:03','dd/mm/yyyy hh24:mi:ss') ptoutor_tracking_time_in, to_date('31/03/2012 14:22:05','dd/mm/yyyy hh24:mi:ss') ptinor_tracking_time_in from dual
     union
     select to_date('31/03/2012 15:00:03','dd/mm/yyyy hh24:mi:ss') ptoutor_tracking_time_in, to_date('31/03/2012 14:22:05','dd/mm/yyyy hh24:mi:ss') ptinor_tracking_time_in from dual
     union
     select to_date('31/03/2012 15:28:00','dd/mm/yyyy hh24:mi:ss') ptoutor_tracking_time_in, to_date('31/03/2012 14:20:00','dd/mm/yyyy hh24:mi:ss') ptinor_tracking_time_in from dual
     union
     select to_date('31/03/2012 15:32:00','dd/mm/yyyy hh24:mi:ss') ptoutor_tracking_time_in, to_date('31/03/2012 14:20:00','dd/mm/yyyy hh24:mi:ss') ptinor_tracking_time_in from dual
     union
     select to_date('31/03/2012 16:00:03','dd/mm/yyyy hh24:mi:ss') ptoutor_tracking_time_in, to_date('31/03/2012 15:22:05','dd/mm/yyyy hh24:mi:ss') ptinor_tracking_time_in from dual
     union
     select to_date('31/03/2012 17:00:03','dd/mm/yyyy hh24:mi:ss') ptoutor_tracking_time_in, to_date('31/03/2012 15:22:05','dd/mm/yyyy hh24:mi:ss') ptinor_tracking_time_in from dual
    )
    select to_char(ptoutor_tracking_time_in, 'dd/mm/yyyy hh24:mi:ss'), to_char(ptinor_tracking_time_in, 'dd/mm/yyyy hh24:mi:ss'),  timedif * 1440 minutes
    from
    (
    select  ptoutor_tracking_time_in, ptinor_tracking_time_in,
    case
    when ptoutor_tracking_time_in - trunc(ptoutor_tracking_time_in)  > (15.5)/24
    then (trunc(ptoutor_tracking_time_in) + (15.5)/24 ) - ptinor_tracking_time_in
    else
     ptoutor_tracking_time_in   - ptinor_tracking_time_in
    end
    timedif
    from tr
    )
    order by ptoutor_tracking_time_in
/
0
 

Author Closing Comment

by:MIREESE
ID: 37792128
That worked perfectly.

Thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question