Time DIfference

Toad for Oracle
1

Greetings!

I am trying to figure out time difference between two columns with military times in it. Time difference should be in minutes. The table that contains these two time columns looks like this:

BEGIN_TIME END_TIME
0800 0850
0900 1015
1030 1300
1100 1430

What I would like to see is

BEGIN_TIME END_TIME MINUTES
0800 0850 50
0900 1015 75
1030 1300 150
1100 1430 210

Time overlapping does not matter. I was not able to figure out how to get time difference between these two times (END_TIME - BEGIN_TIME) in military time. So I thought converting military time to standard time would help. I figured out how to do that.

SELECT

TO_CHAR(TO_DATE(BEGIN_TIME1, ‘HH24MI’), ‘HHMI’) STARTTIME,

TO_CHAR(TO_DATE(END_TIME1, ‘HH24MI’), ‘HHMI’) ENDTIME

I am still not able to figure out how to get the time difference between these two times in minutes. Any assistance provided would be appreciated. I am using Toad 9.7 & Oracle 11g,

2

Afternoon JollyDoe,

what is the DESCRIBE for the table please? I’m interested in knowing if the times you have given above are VARCHAR2 or DATE (or something else entirely!)

What is the version of Oracle too, that can be relevant.

So, assuming (always a bad idea) that your columns are DATEs and that you wish to subtract one time from the other and get a response in minutes, you need a little work.

Subtracting two DATEs gives a result in DAYs.

Multiplying the result in DAYs by the number of minutes in a day should give you what you want.

So,

select (END_TIME1 - BEGIN_TIME1) * (246060)

from table

where …;

should give you the answer. The above used 246060 as it’s easy to see what’s going on, but you could save a microscopic amount of time in the processing of the query by substituting 86400 for (246060) if you wish.

If your columns are VARCHAR2s then you have more work to do to convert them to dates.

select (to_date(end_time1, ‘HH24MI’) - to_date(begin_time1, ‘HH24MI’) * (246060)

from table

where …;

Should work.

As an aside, always always always use the correct data type for storing your data in the database.

Dates and/or Times should be DATE, or TIMESTAMP or TIMESTAMP WITH TIME ZONE etc.

Character data should be CHAR, VARCHR2, NVARCHAR2 or NCHAR etc.

Numbers should be an appropriate NUMBER(x,y) to set the correct scale and precision.

And so on.

This is especially true when using said columns in foreign key constraints etc. And the should (aka MUST!) match in descriptions across each table. If you have the parent table with VARCHAR2 and the child table(s) with NUMBER or DATE then you will cause no end of (a) potential problems and (b) performance issues and © optimiser troubles as the optimiser cannot properly optimise joined columns where implicit (or explicit) conversion functions (like to_date or to_char etc) have to be applied to one column to get it to match the other.

I have been known to rant on and on about this “incorrect usage of data types” problem at some length, I apologise now if this doesn’t apply to you! :wink:

Have fun, and good luck.

HTH

Cheers,

Norm. [TeamT]

3

Cheers Norm,

Thanks for responding promptly and explaining in detail. Let me see if I can answer all your questions.

I am using Toad 9.7 & Oracle 11g.

Data types for these two fields are as follows:

Column Data Type Null?

BEGIN_TIME1 Varchar2(4char) Y

END_TIME1 Varchar2(4char) Y

Copying & pasting the second suggestion (based on varchar2), my query looks like this:

SELECT TO_CHAR(TO_DATE(BEGIN_TIME1, ‘HH24MI’), ‘HHMI’) STARTTIME,

TO_CHAR(TO_DATE(END_TIME1, ‘HH24MI’), ‘HHMI’) ENDTIME,

TO_DATE(END_TIME1, ‘HH24MI’) - TO_DATE(BEGIN_TIME1, ‘HH24MI’) * (246060) MINUTES

FROM Table

WHERE TERM_CODE_KEY = ‘201510’

GROUP BY BEGIN_TIME1, END_TIME1

ORDER BY BEGIN_TIME1, END_TIME1

;

Running this query, I get the following error

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

I did not have any say in designing the table since it was created a long time ago before I started working here.

4

Hi Jolly Doe.

OK, your data is in character format, not good, but we can work with it. :wink:

Your select query is converting from character, to date, then back to character again for the starttime and endtime columns. They are already in character, so just select the two columns as they are, no need go to_char and to_date.

You need brackets round the subtraction of the two to _ dates for the time difference. Because multiplication has higher precedence than subtraction, oracle is multiplying the end time as a date by the number of minutes in a dy, rather than the result of the subtraction, which will be a number!

SELECT BEGIN_TIME1 as BEGINTIME,

    END_TIME1 AS ENDTIME,

    ( TO_DATE(END_TIME1, 'HH24MI') - TO_DATE(BEGIN_TIME1, 'HH24MI') ) * (24*60*60) MINUTES ...

Should do the necessary. I think, I’m not at my computer at the moment, do the above us off the top of my head.

Cheers,

Norm [ TeamT ].

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.

5

Thank you Norm! Your suggestion worked. The only change I had to make in your suggestion was removing the last 60 because it was converting minutes into seconds.

Much appreciated.

6

Nice!

Yes, sorry, 266060 is indeed seconds in a day not minutes. Apologies for that little slip up.

Glad I could help.

Cheers,

Norm [ TeamT ].

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.