Number of miliseconds between now and ...

foxrafi · #1 (**permalink**) 09-13-11, 10:15

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

ARWinner · #2 (**permalink**) 09-13-11, 11:22

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

tonkuma · #3 (**permalink**) 09-13-11, 14:26

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.

foxrafi · #4 (**permalink**) 09-13-11, 16:32

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

tonkuma · #5 (**permalink**) 09-13-11, 16:57

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)

foxrafi · #6 (**permalink**) 09-14-11, 03:40

Quote:

Originally Posted by tonkuma

....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

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.

foxrafi · #7 (**permalink**) 09-14-11, 14:22

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.

tonkuma · #8 (**permalink**) 09-14-11, 20:17

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech