db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dag.wan...@oracle.com (Dag H. Wanvik)
Subject Re: Unix-like timestamp extraction
Date Wed, 25 May 2011 21:08:04 GMT
Loic Petit <loic.petit@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

Mime
View raw message