Forum Stats

  • 3,768,301 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

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

User_8CW5L
User_8CW5L Member Posts: 4 Green Ribbon

Hello Guys,

I came across one scenario of train station data; travelers tap in and tap out data in a day. I have two columns along with the date column one is TAP_IN (can say entering hour at the train station) and TAP_OUT (can say exit time from train station). Both columns are mentioning the only day/night time in 24HH: MM AM/PMformate. So how can I find out the difference in minutes between these two columns (difference between TAP_IN and TAP_OUT results into total minutes)?

Best Answer

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 125 Silver Badge
    Accepted Answer

    Subtraction of 2 values of datatype date gives the time difference in days. So you can use a date column directly and have to convert a string to date first. An example:

    with text_in_out

    as (select '11: 55 AM' t_in, '01: 34 PM' t_out from dual)

     , t_in_out

    as

    (select sysdate tap_out,  trunc(sysdate, 'HH') tap_in

    , 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

    from text_in_out)

    select

    to_char(tap_in, 'HH: MI AM') tap_in, to_char(tap_out,'HH: MI AM') tap_out, (tap_out - tap_in) * 24 * 60 min_d,

    to_char(tap_in_t, 'HH: MI AM') tap_in_t, to_char(tap_out_t,'HH: MI AM') tap_out_t, (tap_out_t - tap_in_t) * 24 * 60 min_t

    from t_in_out

    -- if you store as date (inclusive seconds as with sysdate) you may want to trunc your result in minutes

    User_8CW5L

Answers

  • cormaco
    cormaco Member Posts: 1,721 Bronze Crown

    What is the exact datatype of these two columns?

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 125 Silver Badge
    Accepted Answer

    Subtraction of 2 values of datatype date gives the time difference in days. So you can use a date column directly and have to convert a string to date first. An example:

    with text_in_out

    as (select '11: 55 AM' t_in, '01: 34 PM' t_out from dual)

     , t_in_out

    as

    (select sysdate tap_out,  trunc(sysdate, 'HH') tap_in

    , 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

    from text_in_out)

    select

    to_char(tap_in, 'HH: MI AM') tap_in, to_char(tap_out,'HH: MI AM') tap_out, (tap_out - tap_in) * 24 * 60 min_d,

    to_char(tap_in_t, 'HH: MI AM') tap_in_t, to_char(tap_out_t,'HH: MI AM') tap_out_t, (tap_out_t - tap_in_t) * 24 * 60 min_t

    from t_in_out

    -- if you store as date (inclusive seconds as with sysdate) you may want to trunc your result in minutes

    User_8CW5L
  • User_8CW5L
    User_8CW5L Member Posts: 4 Green Ribbon

    Hi Cormaco,

    Thanks for reverting back. but I was solving a case study in an interview test. So I actually don't know what the data type. I had been given just 2 tables and need to develop queries. In that table, date and time (tap_in and tap_out ) are separately given. So was also confused that do I need to convert those columns for the safer side into another datatype or not?

  • User_8CW5L
    User_8CW5L Member Posts: 4 Green Ribbon

    Hi Hub Tijhuis,

    Thank you for taking the time and proving a solution for this senario.

    This will going to help me and now I am clear what to do in such senario.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond

    Hi, @User_8CW5L

    Both columns are mentioning the only day/night time in 24HH: MM AM/PMformate. 

    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.

    User_8CW5L
  • User_8CW5L
    User_8CW5L Member Posts: 4 Green Ribbon

    Hi @Frank Kulash

    Thank you for your clarification and better explanation. It will help me to deal with various scenarios.