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
