Hey everyone,

I use derby to store monitoring data. As I wanted to see the performance of my service, I have created a table with multiple timestamps that corresponds to different points of the computing.
In order to measure the delay I want to do a timestamp difference in milliseconds.

I read some issues on this topic on this mailling list but I did not find any answer as TIMESTAMPDIFF is limited to the second's resolution.
Actually, I just needed to convert the timestamp to a unix timestamp (as there exists on plenty of other dbms) and then performs the difference with sql. But it seems that such function does not exist.

What I wrote is this :

import java.sql.Timestamp;

public class Util {
    public static long unixTimestamp(Timestamp t) {
        return t.getTime();
    }
}

Integrate this class in the classpath, and then create the function with

CREATE FUNCTION UNIX_TIMESTAMP ( T TIMESTAMP )
RETURNS BIGINT
PARAMETER STYLE JAVA
NO SQL LANGUAGE JAVA
EXTERNAL NAME 'Util.unixTimestamp'

But this is not a very clean way. In my case it is not that problematic to integrate this class in the server but in many other applications it may be a problem.
As this function seems to be really core and really easy to implement (I suppose that the timestamp are somewhat stored as bigints), I suggest to have it as a builtin function.

Regards

Loic Petit
Orange Labs