Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    93

    Unanswered: Calculate time difference between 2 timestamp fileds

    I ran the sql like this.

    select, TIME(FieldA), TIME(FieldB)
    (Time(Field A) - Time(FieldB)) as NET

    The results were

    18:35:47, 18:32:45, 302

    I want to show the NET as 3:02 not 302.

    Please help...

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You probable should create a UDF to do what you want:

    CREATE FUNCTION PRODUCTION.TIME_DIFF(T1 TIME,T2 TIME)
    RETURNS VARCHAR(8) LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
    BEGIN ATOMIC
    DECLARE tDIFF DECIMAL(6,0);
    DECLARE T_STRING VARCHAR(8);
    DECLARE HH char(2);
    DECLARE MM char(2);
    DECLARE SS char(2);

    SET tDIFF = T1 - T2;

    SET HH = SUBSTR(DIGITS(HOUR(tDIFF)),9);
    SET MM = SUBSTR(DIGITS(MINUTE(tDIFF)),9);
    SET SS = SUBSTR(DIGITS(SECOND(tDIFF)),9);

    SET t_STRING = HH || ':' || MM || ':' || SS;

    RETURN T_STRING;

    END@


    HTH

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Calculate time difference between 2 timestamp fileds

    Originally posted by mgupta
    I ran the sql like this.

    select, TIME(FieldA), TIME(FieldB)
    (Time(Field A) - Time(FieldB)) as NET

    The results were

    18:35:47, 18:32:45, 302

    I want to show the NET as 3:02 not 302.

    Please help...
    The result of subtracting one time value from another is timestamp duration, which is a DECIMAL(20,6) in the format:

    yyyymmddhhmmss.zzzzzz, where yyyy, mm, dd, hh, mm, ss, and zzzzzz represent, respectively, the number of years, months, days, hours, minutes, seconds, and microseconds.

    This basically means that if you want any fancy formatting of this number you'll have to do it yourself. For example, you could convert it to a string and then parse the string to extract minutes and seconds.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: Calculate time difference between 2 timestamp fileds

    Originally posted by n_i
    The result of subtracting one time value from another is timestamp duration, which is a DECIMAL(20,6) in the format:

    yyyymmddhhmmss.zzzzzz, where yyyy, mm, dd, hh, mm, ss, and zzzzzz represent, respectively, the number of years, months, days, hours, minutes, seconds, and microseconds.

    This basically means that if you want any fancy formatting of this number you'll have to do it yourself. For example, you could convert it to a string and then parse the string to extract minutes and seconds.
    Actually subtracting times (not timestamps) is a time duration which is DECIMAL (6,0). Subtracting timestamps is a timestamp duration as you describe.

    Andy

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Calculate time difference between 2 timestamp fileds

    will output from TIMESTAMPDIFF function make the customization any easier ?

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Oct 2003
    Posts
    93
    thanks for the responses.
    I tried using timestampdiff. It works but the arguments options a re limited.
    ie it will either give in min or seconds etc. No HH:MMS format..


    anyway thanks a ton to all for the great responses.

Posting Permissions

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