Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 159 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 471 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How to find out difference between two time of the same day

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
-
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
Answers
-
What is the exact datatype of these two columns?
-
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
-
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?
-
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.
-
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.
-
Hi @Frank Kulash
Thank you for your clarification and better explanation. It will help me to deal with various scenarios.