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-----
