Home » Developer & Programmer » Forms » Arithmatic Operation on Time (Form6i Oracle 8i)
Arithmatic Operation on Time [message #437070] Wed, 30 December 2009 21:06 Go to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Dear fellow,

I have 3 text items for shift timestart, timeoff and
timeconsumed. All 3 items are date datatype and i have given
them HH:MI AM format Mask and we are using 12 Hours day.

The requirement is when user enter timestart as 0910, timeoff as
0200 then a display item should show the time consumed i.e.
timeoff - timestart.

in Key-Next-item trigger when i write
timeconsumed := timestart - timeoff

and compile it generates error 'expression is of wrong type'.

Would you please help me that how we can use 12 hours format and how can perform arithmatic operation on 2 date datatype specially using time?

Please help me. As i have a little bit experience of oracle but trying to learn and develop some usefull database.

Muhammad Khalil

[Updated on: Wed, 30 December 2009 21:08]

Report message to a moderator

Re: Arithmatic Operation on Time [message #437075 is a reply to message #437070] Wed, 30 December 2009 22:32 Go to previous messageGo to next message
calluru
Messages: 17
Registered: July 2009
Location: Austin, TX, USA
Junior Member
Hi,

"date - date" will return a number. So TimeConsumed cannot be a date datatype. The return value will be a decimal number. A simple search should return several solutions on how to convert this decimal number into some meaningful interval value.

-- Balaji
Re: Arithmatic Operation on Time [message #437102 is a reply to message #437075] Thu, 31 December 2009 01:45 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just a little notice: "date - date" is a number of days between those two dates (so that you'd know where to start from).
Re: Arithmatic Operation on Time [message #437151 is a reply to message #437102] Thu, 31 December 2009 04:45 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Again i would like to repeat my question and assignment statement.
Would you please help me that how we can use 12 hours format
and how can perform arithmatic operation on 2 date datatype
specially using time?
:Timeconsumed := :timeoff - :timestart;

While compiling the above statement the error
'expression is of wrong type' show with :timeoff.

Muhammad Khalil

[Updated on: Thu, 31 December 2009 04:46]

Report message to a moderator

Re: Arithmatic Operation on Time [message #437162 is a reply to message #437151] Thu, 31 December 2009 06:44 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What exactly did you not understand of what has already been said?

Date (or "time", although that is still the DATE dataatype, never mind the format you chose) subtraction returns a number (number of days, actually), not a date. Therefore, you'll need to convert that number into format you are interested in.

This job requires some calculation ("how to convert a decimal number (of days) into hours:minutes format"?", but that's not really a Forms problem. Perhaps you should search SQL & PL/SQL Forum for some examples, if you are uncertain of how to do that. The basics are simple: 1 day = 24 hours, 1 hour = 60 minutes, etc.

Now it is your turn.
Re: Arithmatic Operation on Time [message #437207 is a reply to message #437162] Fri, 01 January 2010 05:45 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thanks Littlefoot for your detailed and meaningfull elaboration.
I did the job as: timeconsumed Varchar2, Timestart Date, Timeoff
Date. Now write the statment.
:timeconsumed := to_char(:timeoff,'HH:MI AM') - to_char(:timestart,'HH:MI AM');

The timestart and timeoff have format mask HH:MI AM. When i enter the data as 09:10 AM for timestart and 11:00 AM for timeoff the result which display in timeconsumed textitem is ######## where the length of timeconsumed is 10 characters. Where is the problem please guide me.

Muhammad Khalil

[Updated on: Fri, 01 January 2010 05:49]

Report message to a moderator

Re: Arithmatic Operation on Time [message #437208 is a reply to message #437207] Fri, 01 January 2010 06:20 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You didn't get it, I'm afraid. You can't subtract CHARACTERS if you want to get the result; subtract DATES, and adjust the output.

Here's an SQL*Plus example (dates are DD.MM.YYYY HH24:MI):
SQL> desc test
 Name                                               Null?    Type
 -------------------------------------------------- -------- --------------
 TIMESTART                                                   DATE
 TIMEOFF                                                     DATE
 TIMECONSUMED                                                DATE

SQL> l
  1  select
  2    timestart,
  3    timeoff,
  4    -- difference is number of days
  5    timeoff - timestart no_days,
  6    -- number of hours = no_days * 24
  7    (timeoff - timestart) * 24 no_hours_and_min,
  8    -- remove minutes from hours
  9    trunc((timeoff - timestart) * 24) no_hours,
 10    -- number of minutes = the rest of hours * 60
 11    (((timeoff - timestart) * 24) - trunc((timeoff - timestart) * 24)) * 60 no_minutes
 12* from test
SQL> /

TIMESTART        TIMEOFF             NO_DAYS NO_HOURS_AND_MIN   NO_HOURS NO_MINUTES
---------------- ---------------- ---------- ---------------- ---------- ----------
01.01.2010 08:20 01.01.2010 10:25 .086805556       2.08333333          2          5

See? All you have to do is subtract and multiply with certain figures (as I've said, 1 day = 24 hours, 1 hour = 60 minutes, ...). The result you need is NO_HOURS and NO_MINUTES (2 hours 5 minutes).

To do that, apply TO_CHAR to format '2' and '5' to '02:05':
SQL> select
  2    lpad(to_char(trunc((timeoff - timestart) * 24)), 2, '0') no_hours,
  3    lpad(to_char(round((((timeoff - timestart) * 24) - trunc((timeoff - timestart) * 24)) * 60)), 2, '0') no_minutes

  4  from test;

NO_HOURS NO_MINUT
-------- --------
02       05

SQL> select
  2    lpad(to_char(trunc((timeoff - timestart) * 24)), 2, '0') ||
  3    lpad(to_char(round((((timeoff - timestart) * 24) - trunc((timeoff - timestart) * 24)) * 60)), 2, '0') hours_minutes
  4  from test;

HOURS_MINUTES
----------------
0205

SQL> select
  2    to_date(
  3      lpad(to_char(trunc((timeoff - timestart) * 24)), 2, '0') ||
  4      lpad(to_char(round((((timeoff - timestart) * 24) - trunc((timeoff - timestart) * 24)) * 60)), 2, '0'),
  5      'hh24:mi'
  6      ) hours_minutes
  7  from test;

HOURS_MINUTES
----------------
01.01.2010 02:05

OK; let's now update the table:
SQL> update test set
  2    timeconsumed =
  3    to_date(
  4      lpad(to_char(trunc((timeoff - timestart) * 24)), 2, '0') ||
  5      lpad(to_char(round((((timeoff - timestart) * 24) - trunc((timeoff - timestart) * 24)) * 60)), 2, '0')
  6      'hh24:mi'
  7      );

1 row updated.

Finally, the result, selected with TO_CHAR to display it as you'd want it to:
SQL> select timestart, timeoff, to_char(timeconsumed, 'hh24:mi') cons
  2  from test;

TIMESTART        TIMEOFF          CONS
---------------- ---------------- -----
01.01.2010 08:20 01.01.2010 10:25 02:05
Re: Arithmatic Operation on Time [message #437278 is a reply to message #437208] Sat, 02 January 2010 22:52 Go to previous message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Dear Littlefoot,

Sonice of you. So sweet. Thanks a lot. Now it is clear and i
hope that my problem of date operation have been solved for ever
because i have got the concept with your guidance and so
detailed solution. Thanks once again.

Muhammad Khalil
Previous Topic: pls help
Next Topic: Optimal configuration for slow connection users
Goto Forum:
  


Current Time: Sun Sep 25 11:10:12 CDT 2016

Total time taken to generate the page: 0.14661 seconds