Java Development Tools

It's better to copy and paste text from sqlplus rather than an image. Easier for the rest of us to see and work with.

The result looks correct to me. It might not be in the format you want, but you never said what that was.

Your output is in hours, with everything after the comma being decimal, not minutes.

Result looks fine to me.  Your result is decimalised number of hours.

For example 5:31 - 0:00 = 5 hours and 31 minutes = Just over 5 and half hours = 5.51666667

What do you think is wrong with it?

I need the result in format 24hh:MI.

So, when a person will read the report must ne,w that task lasted 4:34 it means 4 hours and 34 minutes.

Paul.

Several ways to do this...  Intervals might make things easier for you to understand.

with test(start_zad, stop_zad) as (

  select sysdate, sysdate + 0.51 from dual -- just over half a day, I've assumed you've already converted to date using to_date(..., 'HH24:MI')

)

, spans as (

  select numtodsinterval(stop_zad - start_zad, 'DAY') as elapsed

  from test

)

select

  extract(day from elapsed) * 24 + extract(hour from elapsed) || ':' ||

  extract(minute from elapsed) duration

from spans

If stop_zad and start_zad are always on the same day, then you can remove the

extract(day from elapsed) * 24 +

bit.

2877737 wrote:
Hi,
in my table I have two columns including hour of start tasks (start_zad) and the second, hour when the tasks where ended (stop_zad).
I try to get information how long taks last. The hour is in format 24HH:MI (eg 23:44) and type of columns is varchar2.
My query is as below and I get wrong result 
Thanks for all help
Paul

The fact that you keep the time in a varchar is itself a design flaw.  It should be in a DATE column, which includes time down to the second.

Thanks All, It works.

Paul.

@"EdStevens"

I feel it isn't proper. Could You suggest how it transform before insert to table ? to_date ?

Paul.

2877737 wrote:
EdStevens
I feel it isn't proper. Could You suggest how it transform before insert to table ? to_date ?
Paul.

where does the data originate before it is inserted?  A keyboard can only create strings, so of course you'd use to_date to convert keyboard input to a DATE type.  That's what to_date does. That's it's entire purpose.

I load data from flat file (txt) to data base using Oracle Data Integrator, są I can modify the interface in ODI to convert them. How ?

to_date(start_zad, '24HH:MI') ?

to_date(stop_zad, '24HH:MI') ?

Paul.

What do you want the date part of those dates to be?

That to_Date will give the 1st day of the current month, is that what you want?

Of course NOT. I need time of start and end tasks.

From flat file I get it varchar2 (eg 21:22)

You seem to be missing the point.

Dates contain dates. Always.

If you only specify the time and not the date in to_date then oracle will default the date to the first of the current month.

You have to have to have some date, if it's not that then it has to be something else.

And you need to tell oracle what that other date is.

If you don't know how to work out the date you want in code then you need to tell us what date you want so we can help.

Hmmmmm,

I have in my table two columns type varchar2 where I insert time of start the tasks and time of end tasks.

When I was creating this table I tried to make this columns as type of date or timestamp and convert varchar2 to these formats during inserting to table.

But I had problems so I gave up it.

Text in a flat file is not really varchar2, it is just text. Part of the question is: is the date in that flat file? And the second part would be, in what way is it in that file. If your flat file has an entry like:

other columns,2018-02-18 22:10, other columns  (assuming columns delimited in some way, rather than fixed column format)

then you should be able to just specify a format to read the entire column into a date in your table. If your flat file has:

other columns,2018-02-18, 22:10, other columns

i.e. it has the date in one column, and the time in another, then it is probably a little more complicated to get the date into one column in your table. I have no idea how ODI works. In SQL LDR you would do something like the following:

LOAD DATA

INFILE *

INSERT

INTO TABLE TESTDATE

fields terminated by ','

trailing nullcols

(

INPUTCOL1 BOUNDFILLER

,INPUTCOL2 BOUNDFILLER

,mystring

,mydate date  'yyyy-mm-dd hh24:mi' ":INPUTCOL1||:INPUTCOL2"

)

BEGINDATA

2018-02-18,10:15,"Hello"

2018-03-18,22:15,"Goodbye"

If the date is not in the flat file, then where does it come from? Are you always loading today's data? Do you have to supply it manually by extracting it from the file name?

Hi @"JonWat",

it's a simple record from a flat file :

17334;UGGG115W;2017-11-30;J57738;23:55;00:00;4.33

I marked start time (23:55) and end time (00:00)

2877737 wrote:
Hi JonWat,
it's a simple record from a flat file :
17334;UGGG115W;2017-11-30;J57738;23:55;00:00;4.33
I marked start time (23:55) and end time (00:00)

So what does start time and end_time actually represent?  Is it the actual time something started/ended?  Or is it when something is scheduled to stop/end regardless of date?

If the former, then it actually started/stopped at the specified time on a particular date - even if you didn't actually capture the date.  And if that is the case, why didn't you capture the date as well?  Or is that what is in the third field?  the '2017-11-30' in your example.  If so, then you need to concatenate that to your time part when you feed it to to_date.  So what is that apparent date, and what are you doing with it now?

Well there is a date in that data - 2017-11-30 - do the start and end times correspond to that date?

no, the date when a task was started is 17334 (YYDDD) at 23:55. But this task was ended at 00:00 on 17335

Assuming duration < 24 hours:

WITH T AS (

          SELECT 17334 START_YYDDD,'23:55' START_ZAD,17335 END_YYDDD,'00:00' END_ZAD FROM DUAL

          )

SELECT  TO_CHAR(

                DATE '1-1-1' +

                (TO_DATE(END_YYDDD || ' ' || END_ZAD,'YYDDD HH24:MI') - TO_DATE(START_YYDDD || ' ' || START_ZAD,'YYDDD HH24:MI')),

                'HH24:MI'

              ) DURATION

  FROM  T

/

DURAT

-----

00:05

SQL>

SY.

That illustrates the problem (one of the problems, anyway) perfectly.

You have a date, 2017 - day #334 - time 23:55 for start of task.

The task ended at midnight.  time 00:00.    But then YOU say in Reply 20   "on 17335"    -    HOW DO YOU KNOW THAT?   Your input row in the flat file does not show the end DATE, in any format, be it YYDDD or any other. Do you know for sure it is not midnight on date 17336?

Suppose these times show start and end of a shift for airline pilots, or for medical residents, etc. Their shifts can often be over 24 hours. (Yes, they do take breaks for sleeping, eating etc. but the shift is counted from start to end including these breaks.)

Or is it that, in your specific business, the duration of every task is always a few hours at most - never more than 24 hours?

In any case, it seems you are given "date" in the format YYDDD and "time-of-day" in format HH24:MI.  These two can be combined together to get the proper value for a DATE column START_ZAD in Oracle - concatenate them, and give the format model 'YYDDDHH24:MI' to the TO_DATE() function. You just need to figure out / explain what date should be attached to STOP_ZAD.

Thx @salomon, this problem we solved.

Now I try insert 23:55 and 00:00 from txt flat file to proper data format in table.

P.

mathguy napisał(-a):
That illustrates the problem (one of the problems, anyway) perfectly.
You have a date, 2017 - day #334 - time 23:55 for start of task.
The task ended at midnight. time 00:00. But then YOU say in Reply 20 "on 17335" - HOW DO YOU KNOW THAT? Your input row in the flat file does not show the end DATE, in any format, be it YYDDD or any other. Do you know for sure it is not midnight on date 17336? - Yes,I do.
Suppose these times show start and end of a shift for airline pilots, or for medical residents, etc. Their shifts can often be over 24 hours. (Yes, they do take breaks for sleeping, eating etc. but the shift is counted from start to end including these breaks.)
Or is it that, in your specific business, the duration of every task is always a few hours at most - never more than 24 hours? - Yes, never
In any case, it seems you are given "date" in the format YYDDD and "time-of-day" in format HH24:MI. These two can be combined together to get the proper value for a DATE column START_ZAD in Oracle - concatenate them, and give the format model 'YYDDDHH24:MI' to the TO_DATE() function. You just need to figure out / explain what date should be attached to STOP_ZAD. - When START_ZAD < STOP_ZAD it's the same day. When STOP_ZAD < START_ZAD then STOP_ZAD is "start day - YYDDD" + 1 

Can START_ZAD and STOP_ZAD ever be equal? (And in that case, does it mean duration was 0, or exactly 1 day? Or undefined?)

You said all tasks are less than 24 hours. If that means STRICTLY LESS than 24 hours, then START_ZAD can equal STOP_ZAD only if the task took zero hours and zero minutes.

Can START_ZAD and STOP_ZAD ever be equal ?  YES, It means the sam day and task lasted less than 1 min.

I am not sure what the best practice is in this type of situation. In theory, you have an input string, delimited by semicolon:

17334;UGGG115W;2017-11-30;J57738;23:55;00:00;4.33

and from it you must populate a table in an Oracle database. If you archive the input file, then you shouldn't need to keep the original strings (like 17344 and 23:55) in the table; the table should only store data in Oracle-appropriate data types.

In some way or another, you are able to split the input string and get something like this (all values are VARCHAR2).

(Side question: Why do you get both col1 and col3? They are the same date; you shouldn't get the same information twice. At best it's redundant, and at worst it's self-contradictory; or does col3 mean something different from col1, even though in this example they happen to be the same date?)

col1   col2      col3        col4    col5   col6   col7  
17334  UGGG115W  2017-11-30  J57738  23:55  00:00  4.33

And from this, you need to populate START_ZAD and END_ZAD (and other columns). Here is what you can use for START_ZAD and STOP_ZAD:

For START_ZAD:       TO_DATE(col1 || col5, 'yydddhh24:mi')

For STOP_ZAD:         TO_DATE(col1 || col6, 'yydddhh24:mi') + case when col5 <= col6 then 0 else 1 end

thanks @"mathguy" !. It works. I've added 2 new kolumns of date type and reaload data into them.

Now it looks "more profesional"

Paul.