db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <karth...@gmail.com>
Subject Re: Timestamp Arithmetic
Date Tue, 14 Nov 2006 18:55:04 GMT
Jose de Castro wrote:

> I trying to calculate the duration (in seconds) between two 
> timestamps. Historically, I have either used a non-standard function 
> such as DateDiff in SQL Server and MySQL or performed a direct 
> arithmetic calculation (i.e. timestamp1-timestamp2) in PostgreSQL.
>
> I have searched the reference documentation and tried both of the 
> aforementioned solutions with no success. I am running the latest 
> stable Derby build (10.2) on  a Windows XP box.
>
> What is the correct way of doing this in Derby?
>
>
> Thanks in advance,
>
> Jose de Castro
> Senior Software Engineer
> Voxeo Corporation
> jose.decastro@voxeo.com
>
>

Derby has TIMESTAMPDIFF and TIMESTAMPADD functions and is there in the 
Reference manual at:
http://db.apache.org/derby/docs/10.2/ref/rrefjdbc88908.html

ij> create  table atab1(ts timestamp, i int, ts1 timestamp);
ij> insert into atab1 values(CURRENT_TIMESTAMP, 1, CURRENT_TIMESTAMP);
1 row inserted/updated/deleted
ij> insert into atab1 values(CURRENT_TIMESTAMP, 2, CURRENT_TIMESTAMP);
1 row inserted/updated/deleted
ij> select {fn TIMESTAMPDIFF(SQL_TSI_SECOND, ts1,ts)} as TS_DIFF from atab1;
TS_DIFF
-----------
0
0

2 rows selected

HTH,
-Rajesh


Mime
View raw message