You Asked

How can I compare the HH24:MI ONLY where access_history(location_id) = tour_detail (location_id) and get the difference in minutes. Perhaps maybe a function that takes in 2 MMDDYYYY HH24:MI:SS and returns the difference. NULL or -999999 if no corresponding access_history date

I know I can do something like this. But do I have to convert the values to a number to do the comparison. Note the difference can be either + or - minutes or 0 if they have the same HH24:MI

select d 
from my_table
where to_char(d,'HH24') > '16';

select * from tour_detail 
order by tour_id, tour_time 

TOUR_ID   TOUR_TIME         LOCATION_ID 
1         06222020 01:27:55          20
1         06222020 01:55:30          15
1         06222020 02:04:09          10

create table access_history(
   employee_id number(6), 
   card_num varchar2(10),
   location_id number(4),
   access_date date,
   processed number(1) default 0
);

select * from access_history
where employee_id =1
order by access_date;

EMPLOYEE_ID    CARD_NUM    LOCATION_ID  ACCESS_DATE       PROCESSED
          1    QS0655F608  20           06222020 11:55:28         0
          1    QS0655F608  15           06222020 14:32:16         0
          1    QS0655F608  10           06222020 14:32:16         0

and Chris said...

I'm not entirely sure what you're trying to do here.

You can get the difference in days between two dates by subtracting one from the other.

To convert this to minutes, multiply the result by the number of minutes in one day (1,440), e.g.:

with rws as (
  select 
    to_date ( '06222020 14:32:16', 'mmddyyyy hh24:mi:ss' ) d1,
    to_date ( '06222020 02:04:09', 'mmddyyyy hh24:mi:ss' ) d2
  from dual
)
  select d1, d2, 
         round ( d1 - d2, 5 ) days_diff,
         round ( ( d1 - d2 ) * 1440 ) minutes_diff
  from   rws;
  
D1                     D2                     DAYS_DIFF   MINUTES_DIFF   
22-JUN-2020 14:32:16   22-JUN-2020 02:04:09     0.51953            748

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 23, 2020 - 12:52 pm UTC

I have a schedule MMDDYYYY HH24:MI:SS format. A guard comes to the door and swipes his card creating an access_history record.

I want to get the difference in minutes from the schedule time and the access_history time. If the difference is+ or -2 minutes I INSERT a status record of okay. If the difference in minutes is > 2 or <= 4 INSERT an early or late record. If the difference is > +5 or - 5 minutes I INSERT a missed record. In short, I only want to do the math on the HH24:MI part of the records only.

June 23, 2020 - 1:08 pm UTC

I have a schedule MMDDYYYY HH24:MI:SS format

You're storing ACCESS_DATE as a DATE. Oracle Database stores these in an INTERNAL format.

How the date appears when you query it is irrelevant. This is controlled by the NLS_DATE_FORMAT settings for the client.

I only want to do the math on the HH24:MI part of the records only.

So what happens if someone is scheduled to start just before midnight, but arrives shortly after? Do you want the difference to be nearly 24 hours?

I suspect not. In which case the solution above still stands:

- Subtract their access date from the schedule date to get the difference in days
- Multiply this up by the number of minutes in a day (1,440) to get the difference in minutes
- <optional>If this comes out more than one day, modulus the result by 1,440 to get just the minutes</optional>
- Use a case expression to convert the outcome of this to OK, early/late, as necessary

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Comparing HH24:MI and return difference

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs