db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Need to convert CURRENT_TIMESTAMP value to BIGINT
Date Mon, 07 Jan 2008 08:46:26 GMT
Suraj Batuwana wrote:
> My Application is using Apache Derby 10.1.3.2 and it needs to insert current
> time stamp value to a column which is a BIGINT ( it can also be SMALLINT,
> INTEGER or DECIMAL) datatype.
> 
> According to http://db.apache.org/derby/docs/10.2/ref/rrefsqlj33562.html
> time stamp value can't directly converted to BIGINT.So first I convert that
> to CHAR and then try to convert that to BIGINT. But it gave the following
> error
> 
> ERROR 22018: Invalid character string format for type BIGINT.
> 
> SQL scripts used to this is in ij are given below
> 
> ij> CREATE TABLE Testtime (
>         username        VARCHAR(64)     NOT NULL ,
>         timestamp       BIGINT  NOT NULL ,
>         CONSTRAINT Testtime_PK  PRIMARY KEY (username) );
> 0 rows inserted/updated/deleted
> ij>     INSERT INTO Testtime (username, timestamp)
>         VALUES ('testusername',
>         CAST(
>         CAST (CURRENT_TIMESTAMP AS CHAR(100)) AS BIGINT)
>         );
> ERROR 22018: Invalid character string format for type BIGINT.
> 
> 	Is there a way to do that in Derby 10.1.3.2
> 	

Hello Suraj,

The CHAR you get from the timestamp cannot be cast to an integer because 
it contains various "formatting characters" (like colons, periods and 
dashes) and is not a valid number.
I assume you want to obtain the number of seconds since the Unix epoch, 
and if you can't do that in Java something like this might work for you:

ij> values {fn 
TIMESTAMPDIFF(SQL_TSI_SECOND,timestamp('1970-1-1-00.00.00.000000'), 
current_timestamp)};
1
--------------------
1199698420

1 row selected

No need to cast this explicitly if you use it in you insert statement.
Maybe there is an easier way as well?



hth,
-- 
Kristian

Mime
View raw message