Home » Developer & Programmer » Forms » time in oracle form 10g
time in oracle form 10g [message #582484] Thu, 18 April 2013 13:08 Go to next message
irfankundi786@yahoo.com
Messages: 221
Registered: February 2009
Location: pakistan
Senior Member

i have two field one is start_time and end_time to enter the task start_time and end_time.
Now whats is datatype is should keep in oracle form and what at the database feild.
i want enter 09:30 start_time and 12:30 to end_time and in third field the difference of these two fields.
Re: time in oracle form 10g [message #582495 is a reply to message #582484] Thu, 18 April 2013 15:35 Go to previous messageGo to next message
joy_division
Messages: 4530
Registered: February 2005
Location: East Coast USA
Senior Member
DATE column, DATETIME text item property with proper format mask.
Different between 2 DATEs is number of days.
Fairly simple calculation to get hours, minutes and seconds.
Re: time in oracle form 10g [message #582501 is a reply to message #582495] Thu, 18 April 2013 21:09 Go to previous messageGo to next message
irfankundi786@yahoo.com
Messages: 221
Registered: February 2009
Location: pakistan
Senior Member

but my requirement is to calculate the time not the days...it means that if start time is 9 am and end time is 12 pm then the its result will be 12-9=3 but when i do it ...it subract data from date...not time how??
Re: time in oracle form 10g [message #582507 is a reply to message #582501] Fri, 19 April 2013 00:18 Go to previous messageGo to next message
Littlefoot
Messages: 19694
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Joy Division explained everything, but you didn't understand what he said. Don't subtract NUMBERS, subtract DATES.
SQL> with diff as
  2    (select to_date('19.04.2013 03:00 pm', 'dd.mm.yyyy hh:mi am') -
  3            to_date('19.04.2013 09:00 am', 'dd.mm.yyyy hh:mi am') days
  4     from dual
  5    )
  6  select days,
  7         days * 24 hours
  8  from diff;

      DAYS      HOURS
---------- ----------
       ,25          6

SQL>
Re: time in oracle form 10g [message #582608 is a reply to message #582507] Sat, 20 April 2013 01:46 Go to previous messageGo to next message
irfankundi786@yahoo.com
Messages: 221
Registered: February 2009
Location: pakistan
Senior Member

i have two fields thats is data type of datetime one for the start_time and other is for the
End_time...i enter the time for 9:30 and 12:0 for two fields but when i going to substract end_time from start_time these substarct the date from each other not the time...because both the field is enter on the same date therefore the result return to 0....
Re: time in oracle form 10g [message #582631 is a reply to message #582608] Sat, 20 April 2013 08:17 Go to previous messageGo to next message
cookiemonster
Messages: 11074
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then you're doing it wrong. Littlefoot gives a working exmaple above.
Re: time in oracle form 10g [message #582669 is a reply to message #582631] Sun, 21 April 2013 01:30 Go to previous messageGo to next message
irfankundi786@yahoo.com
Messages: 221
Registered: February 2009
Location: pakistan
Senior Member

declare
v_start_time number;
begin
select (to_date(mt.start_time,'dd-mm-yyyy hh:mi')- to_date(mt.end_time,'dd-mm-yyyy hh:mi')) into v_start_time
from TASK_MT mt
where mt.time_id=:time_id;
Message('Start time :'||v_start_time);
Message('');
exception
when others then
Message('error is :'||dbms_error_text);
end;

this is return 0 whether start_time is 9:30 and end-time is 12:30
The Result i needed is 3 ...End_time - Start_time
Re: time in oracle form 10g [message #582679 is a reply to message #582669] Sun, 21 April 2013 06:38 Go to previous messageGo to next message
ranamirfan
Messages: 529
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Dear,

Have a look at this post,Very Good Example by Mr.Littlefoot.
http://www.orafaq.com/forum/mv/msg/176831/533259/81953/#msg_533259


Regards,
Irfan
Re: time in oracle form 10g [message #582803 is a reply to message #582669] Mon, 22 April 2013 12:58 Go to previous messageGo to next message
joy_division
Messages: 4530
Registered: February 2005
Location: East Coast USA
Senior Member
irfankundi786@yahoo.com wrote on Sun, 21 April 2013 02:30

select (to_date(mt.start_time,'dd-mm-yyyy hh:mi')- to_date(mt.end_time,'dd-mm-yyyy hh:mi')) into v_start_time


It looks like you are using TO_DATE on a DATE column. This is incorrect.

Additionally, how does subtracting an end time from a start time give you a start time, unless your columns and variables are not well named.
Re: time in oracle form 10g [message #582982 is a reply to message #582803] Wed, 24 April 2013 05:20 Go to previous messageGo to next message
siddiqui_88
Messages: 12
Registered: April 2013
Location: lahore
Junior Member
select substr(to_char(to_date('23-MAR-2013 120000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6) outtime,substr(to_char(to_date('23-MAR-2013 090000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6) intime,
lpad(substr(to_char(to_date('23-MAR-2013 120000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6)-substr(to_char(to_date('23-MAR-2013 090000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6),6,'0') ramain_time
from dual

this will help you dear!
Re: time in oracle form 10g [message #582983 is a reply to message #582982] Wed, 24 April 2013 05:52 Go to previous messageGo to next message
cookiemonster
Messages: 11074
Registered: September 2008
Location: Rainy Manchester
Senior Member
No it won't help. First off your code is hard to read - Please read and follow How to use [code] tags and make your code easier to read?

Secondly, and more importantly, your code doesn't work. If we change the start time to 9:59:00 we get:
SQL> select substr(to_char(to_date('23-MAR-2013 120000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6) outtime,
       substr(to_char(to_date('23-MAR-2013 095900' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6) intime,
  2    3   lpad(substr(to_char(to_date('23-MAR-2013 120000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6)
  4   -substr(to_char(to_date('23-MAR-2013 095900' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6),6,'0') ramain_time
  5   from dual;

OUTTIME            INTIME             RAMAIN_TIME
------------------ ------------------ ------------------
120000             095900             024100

That should be 2 hours 1 minute, not 2 hours 41 minutes.

Littlefoot has shown how it should be done above.
Re: time in oracle form 10g [message #582987 is a reply to message #582982] Wed, 24 April 2013 07:46 Go to previous messageGo to next message
irfankundi786@yahoo.com
Messages: 221
Registered: February 2009
Location: pakistan
Senior Member

irfan ahmad • one another problem i am facing regarding to this post....
i have three fields start_time , End_time And Difference_time means end_time - start_time
now i enter 5 or 6 enteries like

start_time end_time Difference_time
9:00 12:00 3:00
14:00 15:00 1:00
15:00 16:30 1:30

at the end of block i want to sum all defference_time field to calculate all time worked.
now the defference_time is the time datatype having formate mast 24hh:mi
how this can be possible to sum this field??
Re: time in oracle form 10g [message #583035 is a reply to message #582987] Thu, 25 April 2013 00:55 Go to previous messageGo to next message
siddiqui_88
Messages: 12
Registered: April 2013
Location: lahore
Junior Member
are you working on forms or reports

if then use summary column it will help you
Re: time in oracle form 10g [message #583045 is a reply to message #583035] Thu, 25 April 2013 02:42 Go to previous messageGo to next message
cookiemonster
Messages: 11074
Registered: September 2008
Location: Rainy Manchester
Senior Member
@siddiqui_88 - A simple summary column will suffer the exact same problem as your previous attempt at a solution - it'll assume there are 100 minutes in an hour instead of 60.

@irfankundi786@yahoo.com

This is simple maths:
1) add the hours together.
2) Add the minutes togther.
3) Divide minutes by 60 and floor the result to get extra hours
4) Add extra hours to hours total
5) Subtract extra hours * 60 from minutes total to get remaining minutes.
Re: time in oracle form 10g [message #583086 is a reply to message #583035] Thu, 25 April 2013 11:13 Go to previous message
irfankundi786@yahoo.com
Messages: 221
Registered: February 2009
Location: pakistan
Senior Member

i am working on form oracle 10g.
Previous Topic: .fmx in oracle 10g
Next Topic: date format DD / MM / YYYY
Goto Forum:
  


Current Time: Thu Oct 23 12:49:54 CDT 2014

Total time taken to generate the page: 0.11414 seconds