Loic Petit <loic.pe...@gmail.com> writes:
> 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.
You can use the escape function timestampdiff with SQL_TSI_FRAC_SECOND:
ij> select {fn timestampdiff(SQL_TSI_FRAC_SECOND, t, CURRENT_TIMESTAMP)} as
diff , {fn timestampdiff(SQL_TSI_SECOND, t, CURRENT_TIMESTAMP)} as diff2 from t;
DIFF |DIFF2
-----------------------------------------
360976000000 |360
but resolution seems to be only milliseconds.
Thanks,
Dag
>
> 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
