Results 1 to 8 of 8
  1. #1
    Join Date
    May 2011
    Location
    Poland
    Posts
    5

    Question Unanswered: Number of miliseconds between now and ...

    Hi,
    Do you know any other (better) solution for returning the number of miliseconds between current timestamp and "1970-01-01-00.00.00.0000", another than this one:

    select bigint(timestampdiff(2,char(current timestamp - timestamp('1970-01-01-00.00.00')))) * 1000 from sysibm.sysdummy1;

    here is the link to timestampdiff documentation

    Thank you for your help and support
    Respect,
    foxrafi

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    As you are aware, timestampdiff returns an approximate result. If the error is only 1 day off, the result will be off by as much as 86.4 million milliseconds. It gets worse as the error increases. You will need to write a UDF to get a more accurate value.

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 Basics: Fun with Dates and Times

    Add (difference of MICROSECOND) / 1000 to secondsdiff(a sample UDF in the article) to get the number of miliseconds between two timestamps.

  4. #4
    Join Date
    May 2011
    Location
    Poland
    Posts
    5
    Thanks for responses. I also found the following method here:

    This will do micro seconds. Divide by 1000 to get milliseconds...
    CREATE FUNCTION F.MICROSECONDS (X TIMESTAMP, Y TIMESTAMP)
    RETURNS BIGINT
    SPECIFIC F.MICROSECONDS
    LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN
    (DAYS(X) - DAYS(Y)) * BIGINT(86400000000) +
    MIDNIGHT_SECONDS(X) * BIGINT(1000000)
    - MIDNIGHT_SECONDS(Y) * BIGINT(1000000)
    + MICROSECOND(X)
    - MICROSECOND(Y)
    ;

    Do you think this method is correct ?

    Once again thank you for your help
    Foxrafi

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes, I think F.MICROSECONDS is correct.

    But, why was it used the expression
    MIDNIGHT_SECONDS(X) * BIGINT(1000000) - MIDNIGHT_SECONDS(Y) * BIGINT(1000000)
    instead of
    (MIDNIGHT_SECONDS(X) - MIDNIGHT_SECONDS(Y)) * BIGINT(1000000)
    ?

    It is not consistent with the expression
    (DAYS(X) - DAYS(Y)) * BIGINT(86400000000)

  6. #6
    Join Date
    May 2011
    Location
    Poland
    Posts
    5

    Thumbs down

    Quote Originally Posted by tonkuma View Post
    ....why was it used the expression
    MIDNIGHT_SECONDS(X) * BIGINT(1000000) - MIDNIGHT_SECONDS(Y) * BIGINT(1000000)
    instead of
    (MIDNIGHT_SECONDS(X) - MIDNIGHT_SECONDS(Y)) * BIGINT(1000000)
    ?
    I modified the code according to your proposition and it works the same. Probably somebody was in a hurry writing the previous version.

    Quote Originally Posted by tonkuma View Post
    It is not consistent with the expression
    (DAYS(X) - DAYS(Y)) * BIGINT(86400000000)
    Yeah, but this code was delivered by a guy from IBM so I hope it is correct.

    Still I don't understand how IBM can deliver such function like timestampdiff that in my opinion is useless. Is it so difficult to count the number of miliseconds between two timestamps ? I don't think so, there are algorithms to count leap years and leap seconds that should be included in this function.

  7. #7
    Join Date
    May 2011
    Location
    Poland
    Posts
    5
    Here is another possible version:
    BIGINT((BIGINT ((DAYS(X) - 719163) * 86400 + MIDNIGHT_SECONDS(TIME(X)) ))*1000)+MICROSECOND(x)/1000)

    It works as the previous solution.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    BIGINT((BIGINT ((DAYS(X) - 719163) * 86400 + MIDNIGHT_SECONDS(TIME(X)) ))*1000)+MICROSECOND(x)/1000)
    It contains an extra right parenthesis.
    It is not necessary to cast to TIME before applying MIDNIGHT_SECONDS, to cast to BIGINT twice.

    expression_1 is an expression removed rhghtmost parenthesis from your expression.
    expression_2 is an example of revised expression.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT BIGINT((BIGINT ((DAYS(X) - 719163) * 86400 + MIDNIGHT_SECONDS(TIME(X)) ))*1000)+MICROSECOND(x)/1000 AS expression_1
         , ( ( DAYS(x) - BIGINT(719163) ) * 86400 + MIDNIGHT_SECONDS(x) ) * 1000 + MICROSECOND(x) / 1000 AS expression_2
     FROM  (VALUES CURRENT_TIMESTAMP) x(x);
    ------------------------------------------------------------------------------
    
    EXPRESSION_1         EXPRESSION_2        
    -------------------- --------------------
           1316077745499        1316077745499
    
      1 record(s) selected.
    Last edited by tonkuma; 09-14-11 at 21:30. Reason: Replace "parentheses" to "parenthesis"

Tags for this Thread

Posting Permissions

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