You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Jose de Castro <jo...@voxeo.com> on 2006/11/14 18:49:37 UTC
Timestamp Arithmetic
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
Re: Timestamp Arithmetic
Posted by "el.pregunton" <ol...@gmail.com>.
Two questions that the Derby Documentation does not mentions,
What happens when the 1st timestamp is more recent than the 2nd in the
timestampdiff function? and what is the result when the 2nd timestamp is
more recent than the 1st?
thanks
Rajesh Kartha-2 wrote:
>
> 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
>
>
>
--
View this message in context: http://old.nabble.com/Timestamp-Arithmetic-tp7342697p28199979.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Timestamp Arithmetic
Posted by Jose de Castro <jo...@voxeo.com>.
You learn something new every day. A whole aspect of JDBC of which I was
completely unaware.
Thanks Rajesh :)
Rajesh Kartha wrote:
> 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
>
Re: Timestamp Arithmetic
Posted by Rajesh Kartha <ka...@gmail.com>.
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