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
