db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jack Klebanoff <klebanoff-de...@sbcglobal.net>
Subject [PATCH] Timestamp Arithmetic
Date Fri, 13 May 2005 16:16:01 GMT
The attached patch adds support for the JDBC TIMESTAMPADD and 
TIMESTAMPDIFF escape functions. JDBC escape functions may be embedded in 
SQL. JDBC drivers convert them to the syntax used by the underlying 
database. Derby handles JDBC escapes directly in its parser.

The syntax is of TIMESTAMPADD and TIMESTAMPDIFF is

  {fn TIMESTAMPADD( interval, count, ts1)}
  {fn TIMESTAMPDIFF( interval, ts1, ts2)}

where interval is one of SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, 
SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, 
SQL_TSI_QUARTER, or SQL_TSI_YEAR; count is a numeric expression; and ts1 
and ts2 are datetime expressions (date, time, or timestamp). If a date 
is used in ts1 or ts2 it is converted to a timestamp by using time 
00:00:00. If a time is used in ts1 or ts2 it is converted to a timestamp 
by using the current date.

SQL_TSI_FRAC_SECOND indicates nanoseconds. The other interval names are 
self-explanatory.

TIMESTAMPADD produces a new timestamp by adding count intervals to ts1. 
For example
  VALUES( {fn TIMESTAMPADD( SQL_TSI_DAY, 1, CURRENT_TIMESTAMP)})
produces the timestamp for one day from now.

TIMESTAMPDIFF returns the number of intervals by which ts2 exceeds ts2. 
It is approximately ts2 - ts1. TIMESTAMPDIFF produces an integer. If the 
actual difference is too large to fit in an integer then an SQLException 
is thrown. If the actual difference is not an integral number of 
intervals then TIMESTAMPDIFF rounds to 0. For example

  VALUES( {fn TIMESTAMPDIFF( SQL_TSI_HOUR, {t '12:00:00'}, {t '13:50:00'})},
                    {fn TIMESTAMPDIFF( SQL_TSI_HOUR, {t '13:50:00'}, {t 
'12:00:00'})}

produces the row (1, -1).

More examples:
  SELECT * FROM t WHERE {fn TIMESTAMPDIFF( SQL_TSI_DAY, CURRENT_DAY, 
promisedDate)} <= 1
Selects all rows from t with promisedDate at most one day from now. (It 
also selects rows with a promisedDate in the past). Note that this is 
probably not the optimal way to express the query. If promisedDate is 
indexed the Derby optimizer will not use the index. Better is
  SELECT * FROM t WHERE promisedDate <= {fn TIMESTAMPADD( SQL_TSI_DAY, 
1, CURRENT_DAY)

I have run the patch against the derbylang test suite.

The svn status is:

M      tools\jar\DBMSnodes.properties
M      java\engine\org\apache\derby\impl\sql\compile\NodeFactoryImpl.java
M      java\engine\org\apache\derby\impl\sql\compile\C_NodeNames.java
A      
java\engine\org\apache\derby\impl\sql\compile\TimestampArithmeticFnNode.java
M      java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
M      java\engine\org\apache\derby\iapi\sql\compile\C_NodeTypes.java
M      java\engine\org\apache\derby\iapi\types\DateTimeDataValue.java
M      java\engine\org\apache\derby\iapi\types\SQLDate.java
M      java\engine\org\apache\derby\iapi\types\SQLTime.java
M      java\engine\org\apache\derby\iapi\types\SQLTimestamp.java
M      java\engine\org\apache\derby\iapi\reference\SQLState.java
M      java\engine\org\apache\derby\loc\messages_en.properties
A      
java\testing\org\apache\derbyTesting\functionTests\tests\lang\timestampArith.java
A      
java\testing\org\apache\derbyTesting\functionTests\master\timestampArith.out
M      
java\testing\org\apache\derbyTesting\functionTests\suites\derbylang.runall


Jack Klebanoff

Mime
View raw message