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">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-2" http-equiv="Content-Type">
  <title></title>
</head>
<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>
<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>
<br>
ij&gt; select {fn timestampdiff(SQL_TSI_DAY, {d '2000-01-01'}, {d
'2000-01-10'})}<br>
as date_diff from oneRow;<br>
DATE_DIFF<br>
-----------<br>
9<br>
<br>
1 row selected<br>
<br>
Satheesh<br>
<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;
 date_diff
-----------
         9
(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
dict_country;
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? 

  </pre>
</blockquote>
</body>
</html>


Mime
View raw message