Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Exclamation Unanswered: Difference between tow time interval

    Hai..

    i have 2 columns with 'time' datatype in my MySQL table one storing the start time and the other one storing the end time of an event. I store time values in these 2 colums in 'HH:MMS' format.

    i have to find out the time difference between these two columns (endtime - starttime). (ie) the total time interval for which the process was running.

    i am using the following query for that.

    select time_format(etime-stime,'%H') as hour , time_format(etime-stime,'%i') as minutes from tablename";

    this is working fine for values in which the endtime minutes is higher than the starttime minutes, eg (starttime - 08:30:00 and endtime - 10:40:00) but when the endtime minute is less than the starttime minute, (starttime - 08:30:00 and endtime - 10:20:00) i am getting null values. So how do over come this. Please help...

    With regards
    Sudar

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Why not doing this :

    select time_format(etime-stime,'%H:%i') as elapsed_time from tablename;

    ?

    Regards,

    RBARAER

  3. #3
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Unhappy Even this is not working

    hai RBARAER

    thnks fot the reply
    i
    tried even
    select time_format(etime-stime,"%H:%i") from table_name

    even for this also, it is returning NULL values when the minute in etime is less than the minute in stime.


    BTW i am using MySQL V 4.0.17

    with regards
    Sudar

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    This seems odd to me ... In the first case it could have been somewhat logical (and you would not have had the correct result with this query). But the second should work...

    Try this, which will show you the elapsed time in seconds :

    select TIME_TO_SEC(etime) - TIME_TO_SEC(stime) as elapsed_seconds from tablename;

    And if the result seems correct to you, try :

    select TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(etime) - TIME_TO_SEC(stime)), '%H:%i') as elapsed_time from tablename;

    You're sure datatype is well TIME ? Not DATETIME or other ?

    Regards,

    RBARAER

  5. #5
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    thnks

    thnks RBARAER

    your second solution is working fine and has solved my problem..

    thnk u once again

    With regards
    Sudar

Posting Permissions

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