How to find out difference between two time of the same day

Hi, @User_8CW5L

If this is an interview question, the first thing to point out is the unusual format. In real life, '24HH' is never used with 'AM' or 'PM', and 'HH:MM' is a beginner's mistake. ('MM' means month; 'MI' means minutes.) The format should probably be either 'HH24:MI' (without 'AM' or 'PM', meaning a quarter before midnight is '23:45') or 'HH:MI AM' (without '24', meaning a quarter before midnight is '11:45 PM').

If you're given strings in any format, then start, as Hub suggested, by converting strings to DATEs. Hub used

to_date('2000 01 01 ' || t_in,'YYYY MM DD HH: MI AM') tap_in_t, to_date('2000 01 01 ' || t_out,'YYYY MM DD HH: MI AM' ) tap_out_t

to get DATEs on the same calendar day. You could just as well say

to_date(t_in, 'HH: MI AM') tap_in_t, to_date(t_out, 'HH: MI AM' ) tap_out_t

If you don't supply the year, month and/or day, then TO_DATE uses a default. What are those defaults? You actually don't need to know; all you need to know is that the are the same throughout the query. (For the record, 'YYYY' and 'MM' default to the current year and month, as given by SYSDATE at the time the query begins, and 'DD' defaults to '01'.)

Also, in interviews, people want to know how you handle special cases. For example, what happens if someone taps in at 23:45, then taps out 30 minutes later, at 00:15? If tap_in_t and tap_out_t are the converted DATEs, then

(tap_out_t - tap_in_t) * 24 * 60

would be -1410 minutes. If you want to assume that, when tap_in_t is greater than tap_out_t, that means tap in occurred before midnight, and tap out occurred after midnight on the following day, then you can do something like this:

( CASE
   WHEN tap_in_t <= tap_out_t
   THEN tap_out_t
   ELSE tap_out_t + 1
 END
- tap_in_t
) * 24 * 6

But remember, this is making an assumption. If people can actually tap out 12 hours (or more) after they tapped in, then this may not reflect what actually happened.