# Thread: Number of miliseconds between now and ...

1. Registered User
Join Date
May 2011
Location
Poland
Posts
5

## 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. Registered User
Join Date
Jan 2003
Posts
4,310
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. Registered User
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. Registered User
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. Registered User
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)
(MIDNIGHT_SECONDS(X) - MIDNIGHT_SECONDS(Y)) * BIGINT(1000000)
?

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

6. Registered User
Join Date
May 2011
Location
Poland
Posts
5
Originally Posted by tonkuma
....why was it used the expression
MIDNIGHT_SECONDS(X) * BIGINT(1000000) - MIDNIGHT_SECONDS(Y) * BIGINT(1000000)
(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.

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.

7. Registered User
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. Registered User
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"