Need to convert CURRENT_TIMESTAMP value to BIGINT

[Suraj Batuwana <sc...@gmail.com>]

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
	
-- 
View this message in context: http://www.nabble.com/Need-to-convert-CURRENT_TIMESTAMP-value-to-BIGINT-tp14660378p14660378.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Need to convert CURRENT_TIMESTAMP value to BIGINT

[Kristian Waagan <Kr...@Sun.COM>]

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