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 Re: Unix-like timestamp extraction
Date Wed, 25 May 2011 21:15:27 GMT
Oh ! Great :)
That solves my problems.

Thanks about that.

Cheers

LP

Le 25 mai 2011 à 23:08, Dag H. Wanvik a écrit :

> 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