timestamp 2 date in db

C

curwen

Hi all,
I have a unix timestamp (1074074051) to be converted and inserted in a
database.

I tried the following:

Date d = new Date(Long.parseLong(1074074051) * 1000 );
String strSQL ="INSERT INTO MYTABLE values ("+
",to_date("+d.toString()+")"+ etc...

and the result was:

1) if I use java.sql.date:
it converts only yyyy-mm-dd but I need the seconds, to

is there a way to retrieve the secs,too?

2) if I use java.sql.date:
I get a date like:
'Wed Jan 14 10:54:11 GMT+01:00 2004'
I can't find a suitable Oracle format mask for it

plus: I'm living in the GMT+1 zone, so I guess the JVM takes the GMT
from my system
the situation is: the timestamp is in GMT0 time and Java convert in
+1, correct?
so is easier for me store the gmt+1 local time in db

what the correct format mask OR the java manipulation to get an
insertable date string?


3) what's your opinion about my approach?

thanks in advance
jc
 
A

Angus

curwen said:
Hi all,
I have a unix timestamp (1074074051) to be converted and inserted in a
database.

I tried the following:

I suggest you try the following, instead:

java.sql.Timestamp ts = new java.sql.Timestamp(1074074051);
String strSQL ="INSERT INTO MYTABLE values ("?");
java.sql.Connection conn = // however you get a database connection
java.sql.PreparedStatement ps = conn.prepareStatement(strSQL);
ps.setTimestamp(ts);
ps.executeUpdate();

which makes your following first two questions irrelevant.
1) if I use java.sql.date:
it converts only yyyy-mm-dd but I need the seconds, to

is there a way to retrieve the secs,too?

2) if I use java.sql.date:
I get a date like:
'Wed Jan 14 10:54:11 GMT+01:00 2004'
I can't find a suitable Oracle format mask for it

plus: I'm living in the GMT+1 zone, so I guess the JVM takes the GMT
from my system
the situation is: the timestamp is in GMT0 time and Java convert in
+1, correct?
so is easier for me store the gmt+1 local time in db

what the correct format mask OR the java manipulation to get an
insertable date string?


3) what's your opinion about my approach?

I don't think it's good -- which is why I showed you an
alternative, above.

Good Luck,
Avi.
 
A

Angus

Angus said:
I suggest you try the following, instead:

String strSQL ="INSERT INTO MYTABLE values ("?");

WHOOPS! That line is wrong (of-course, D'OH). It should be:

String strSQL ="INSERT INTO MYTABLE values (?)";

Good Luck,
Avi.
 
R

RJG

Hi all,
I have a unix timestamp (1074074051) to be converted and inserted in a
database.

I tried the following:

Date d = new Date(Long.parseLong(1074074051) * 1000 );
String strSQL ="INSERT INTO MYTABLE values ("+
",to_date("+d.toString()+")"+ etc...

and the result was:

1) if I use java.sql.date:
it converts only yyyy-mm-dd but I need the seconds, to

is there a way to retrieve the secs,too?

2) if I use java.sql.date:
I get a date like:
'Wed Jan 14 10:54:11 GMT+01:00 2004'
I can't find a suitable Oracle format mask for it

plus: I'm living in the GMT+1 zone, so I guess the JVM takes the GMT
from my system
the situation is: the timestamp is in GMT0 time and Java convert in
+1, correct?
so is easier for me store the gmt+1 local time in db

what the correct format mask OR the java manipulation to get an
insertable date string?


3) what's your opinion about my approach?

thanks in advance
jc


Your problem here isn't actually a Java one, but an Oracle one. On
the insert statement, use to_date('mm/dd/yyyy hh24miss','xxx') where
xxx is the String representation of the date in the same mask as the
to_date specifies. You can change the mask to whatever you prefer as
long as it matches the results of the java date format. Of course,
you can format the java date variable using SimpleDateFormat.

-RJG-
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top