db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: How to get a difference between two dates?
Date Sun, 07 Aug 2005 18:38:15 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <meta content="text/html;charset=ISO-8859-2" http-equiv="Content-Type">
<body bgcolor="#ffffff" text="#000000">
Derby 10.1 supports datetime arithmetic... Adding a constant interval
to a datetime (timestampadd) or subtracting one datetime with another (<b>timestampdiff</b>).
However, these operators are only present as JDBC escape functions, so
you need to invoke them using standard JDBC function invocation syntax.
( {fn func()}<br>
Check out the (confusing) syntax in 10.1 reference manual:
<a class="moz-txt-link-freetext" href="http://db.apache.org/derby/docs/10.1/ref/refderby.pdf">http://db.apache.org/derby/docs/10.1/ref/refderby.pdf</a><br>
ij&gt; select {fn timestampdiff(SQL_TSI_DAY, {d '2000-01-01'}, {d
as date_diff from oneRow;<br>
1 row selected<br>
Maciej BliziƄski wrote:
<blockquote cite="mid1123399224.1612.4.camel@localhost" type="cite">
  <pre wrap="">Hello everybody,

I have a question about getting a date difference in Derby. I tried
Derby online docs, Google (web and newsgroups) and got no results.

For example, in PostgreSQL I can get a difference of two dates:

SELECT '2000-01-10'::DATE - '2000-01-01'::DATE AS date_diff;
(1 row)

Similar thing with Derby brings an error message:

ij&gt; SELECT DATE('2000-01-10') - DATE('2000-01-01') AS date_diff FROM
ERROR 42Y95: The '-' operator with a left operand type of 'DATE' and a
right operand type of 'DATE' is not supported.

("FROM dict_country" was added only because Derby needs a FROM clause,
while PostgreSQL doesn't)

So, if substraction of two dates is not supported, how can I get a
difference in days between two dates? 


View raw message