db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Loic Petit <loic.pe...@gmail.com>
Subject Unix-like timestamp extraction
Date Wed, 25 May 2011 16:05:48 GMT
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

Mime
View raw message