db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <kar...@Source-Zone.Org>
Subject Re: Using datetime math with derby
Date Tue, 04 Jan 2005 03:17:59 GMT
Hello,

There may be other ways, but here is one - using  functions, hope this 
helps:

ij> connect 'jdbc:derby:timestamptest';
ij> create table atab (id int, col1 Timestamp);
0 rows inserted/updated/deleted
ij> insert into atab values (1,CURRENT_TIMESTAMP);
1 row inserted/updated/deleted
ij> insert into atab values (2,CURRENT_TIMESTAMP);
1 row inserted/updated/deleted
ij> select * from atab;
ID         |COL1
--------------------------------------
1          |2005-01-03 18:53:54.17
2          |2005-01-03 18:53:59.527

2 rows selected
ij> create function toTS(TS VARCHAR(40)) returns BIGINT PARAMETER STYLE 
JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME
'TestTS.getTime';
0 rows inserted/updated/deleted
ij> select toTS(COL1) from atab;
1
--------------------
1104807234017
1104807239527

2 rows selected
ij>

**********************************************
The sample code for the TestTS.getTime() method is as follows. Compile 
it and make sure it is available in the CLASSPATH while using
'ij' or your Java application.


import java.util.Date;
import java.text.SimpleDateFormat;
public class TestTS{
    public static long getTime(String ts) throws Exception {
        try{
            SimpleDateFormat sdt=new SimpleDateFormat("yyyy-MM-dd 
HH:mm:ss.SSSSSS");
            Date d=sdt.parse(ts);
            return d.getTime();
        }catch(Exception e){
            e.printStackTrace();
            throw e;
        }
    }
}

**********************************************


Regards,
Rajesh

Ulrich Voigt wrote:

> Hi!
> The derby documentation points out under "Derby Support for SQL-92 
> Features" that datetime math can be done with Java methods. But I 
> haven't found any hint how to do this.
> My problem is that I want to convert a timestamp (CURRENT_TIMESTAMP) 
> in a SQL statement to the corresponding Long value as it is done with 
> Date.getTime().
> Any hints are welcome!
> Ulrich
>
>


Mime
View raw message