Using Timestampdiff in a Derby Where Clause

Using timestampdiff in a Derby WHERE clause

You can see an example in the original patch email,

SELECT * FROM t WHERE {fn TIMESTAMPDIFF( SQL_TSI_DAY, CURRENT_DAY, promisedDate)} <= 1

handling dates in derby within query

After hours of playing around i sorted out the query.Thanks to all who helped me through!
Here it is:

SELECT EXPIRY,MDATE FROM PRODUCTS WHERE cast( {fn TIMESTAMPDIFF( SQL_TSI_DAY,MDATE,CURRENT_TIMESTAMP)} as double) /cast( {fn TIMESTAMPDIFF( SQL_TSI_DAY,MDATE,EXPIRY )} as DOUBLE)*100 > 70 ;

Solution was to cast the TIMESTAMPDIFF as DOUBLE.With out casting, it could not calculate the percentage..why, that i also am not sure.But some how it worked for me.Any one with knowledge, please do share.Cheers!

timestampdiff between two dates in derby

It seems like you should have two different rows: one is the start row, and one is the end row. Each row should have a transaction id, and a timestamp, and one should have a code that indicates it is the start row, while the other should have a code that indicates it is the end row.

Then, you can select those two rows by self-joining the table on its transaction id, and then you can subtract the start time from the end time to get the elapsed time.

How to query data from timestamp column with delay.?

Here is the answer,

select *from NEWS where {fn TIMESTAMPDIFF( SQL_TSI_MINUTE, NEWS_DATE,current_timestamp)} <120

Does Derby support INTERVAL for date

You can try the TIMESTAMPADD() function:

WHERE l_shipdate <= CAST({fn TIMESTAMPADD(SQL_TSI_DAY, -1, CAST('1998-12-01 00:00:00' AS TIMESTAMP))} AS DATE)

How to get the hour difference between 2 datetimestamp on derby db?

In Derby there is a TIMESTAMPDIFF:

for instance:

select {fn timestampdiff(SQL_TSI_FRAC_SECOND, startdate, enddate)} as diff 

Case insensitive search in Java DB (Derby)

You can use the UPPER() or LOWER() SQL functions on both your search argument and the field, like in

SELECT *
FROM   mytab
WHERE  UPPER(lastname) = UPPER('McDonalds')

---

Related Topics