db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ulrich Voigt <u.vo...@gematronik.com>
Subject Re: Using datetime math with derby
Date Tue, 11 Jan 2005 09:36:58 GMT
Hi,

thanks for your answers. Rajesh answer hit the point.
Dan, the link you gave didn't show a way, how to convert a TIMESTAMP to 
BIGINT.

I am a bit surprised that Derby does not provide any date time math as a 
core function. Think of the follwing problem:
TABLE date_test (
  date1 TIMESTAMP,
  date2 TIMESTAMP,
  value INTEGER # (or something else)
)

Try to query all rows, where date1 and date2 are less than 12 hours 
apart. At the moment I don't know how to realize the query without 
writing Java functions with the disadvantage, that the user must have 
access to the database server.

Ulrich

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Rajesh Kartha wrote:

 > Hello,
 >
 > There may be other ways, but here is one - using  functions, hope this

A simpler function, using the natural mapping of TIMESTAMP to
java.sql.Timestamp. Rajesh's function may have issues with timezone,
where the value returned may be dependent on the timezone of the VM.
For details on how Derby (and JDBC) handles timezones with SQL date/time
types and JDBC date time types see

http://incubator.apache.org/derby/papers/JDBCImplementation.html

SQL

create function toTS(TS TIMESTAMP) returns BIGINT
PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
EXTERNAL NAME 'TestTS.getSimpleTime'

Java

public static long getSimpleTime(java.sql.Timestamp ts) {
        return ts.getTime();
   }


Dan.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFB2scMIv0S4qsbfuQRAltdAJ9OY7CzQzgCBIYIcvVFHJGUlXa0jACZAbAL
nZAnO6tUzmrFxxvu3wwm1Kw=
=vzII
-----END PGP SIGNATURE-----




Mime
View raw message