Results 1 to 2 of 2

Thread: Timestampdiff

  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Unanswered: Timestampdiff

    Hi,

    following is my table:

    ANR START END
    ----- ---------------------------- ------------------
    1 2008-01-14-13.00.00.000000 2008-01-14-13.06.00.000000
    1 2008-01-14-13.05.00.000000 2008-01-14-13.07.00.000000
    1 2008-01-15-17.05.00.000000 2008-01-15-17.34.00.000000
    1 2008-01-16-10.05.00.000000 2008-01-16-10.15.00.000000
    1 2008-01-17-10.05.00.000000 2008-01-17-10.15.00.000000
    1 2008-02-01-10.05.00.000000 2008-02-01-10.15.00.000000
    3 2008-01-16-10.00.00.000000 2008-01-16-10.01.00.000000
    4 2008-01-15-17.10.00.000000 2008-01-15-18.15.00.000000

    the result table is:

    ANR HOURS
    --------- -------
    1 1,1
    3 0,0
    4 1,0


    The tak is to SUM and GROUP BY ANR the time differenz between START end END in hours.

    what i was trying is:

    SELECT SUM(TIMESTAMPDIFF(6,CHAR(ende - beginn)))
    FROM telefonat
    GROUP BY ANR

    the problem is that the result has to be a decimal (.1).

    What i get is an error.

    if i change to

    SELECT SUM(TIMESTAMPDIFF(6,CHAR(ende - beginn)))
    FROM telefonat
    GROUP BY ANR

    i get: (So it works..)

    ANR MINUTES
    --------- -------
    1 67
    3 1
    4 65


    Anyone knows what to do?


    Ben

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You should write your own UDF to compute the difference. TIMESTAMPDIFF will only give an approximation.


    Code:
    values (timestampdiff(4,char(timestamp('02/28/2010','13:00:00') - timestamp('01/27/2010','12:00:00'))))
    
    1
    -----------
          44700
    
      1 record(s) selected.
    
    values (production.udf_ts_difference(timestamp('02/28/2010','13:00:00'),timestamp('01/27/2010','12:00:00'),'MINUTES'))
    
    1
    ----------------------
              46140.000000
    
      1 record(s) selected.

    Andy

Posting Permissions

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