Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Find time difference from current time stamp

    Hi,

    I have one table where one column is used to store time in the format 'hh:mm:ss'.

    I would like to find the time difference between that column of that table and current time stamp.

    It would be nice if you can suggest any solution for the above requirement.


    Thanks in advance,
    JD

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Which of the 360,000 suggestions on Google have you tried?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jan 2004
    Posts
    153
    Quote Originally Posted by cis_groupie View Post
    Which of the 360,000 suggestions on Google have you tried?

    below is the solution I did :

    function fn_get_remaining_time(p_i_n_var in varchar2)
    return varchar2
    as
    l_n_filled_time_mnt number :=0;
    l_n_sys_time_mnt number :=0;
    l_n_tm_diff number;
    l_v_remain_time varchar2(6);

    begin
    l_n_filled_time_mnt := (substr(p_i_n_var,1,2) * 60) + substr(p_i_n_var,4,2);

    l_n_sys_time_mnt := (to_char(sysdate,'hh') * 60) + to_char(sysdate,'mm');

    l_n_tm_diff := l_n_filled_time_mnt - l_n_sys_time_mnt;

    if l_n_tm_diff > 60 then
    l_v_remain_time := trunc(l_n_tm_diff/60,0)||':'||round(mod(l_n_tm_diff,60),2);
    else
    l_v_remain_time := 0||':'||l_n_tm_diff;
    end if;

    return l_v_remain_time;
    exception
    when others then
    return '00:00';

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    having a string containing just the hour, minute, and second is useless. what happens if the time is '12:13:14' and it is 9am, do you return a negative. What happens if the record was written yesterday, do you return over 24 hours?

    Always store a timestamp as a date or timestamp column. NEVER store is as a text string.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •