timestamp 2 date in db

Discussion in 'Java' started by curwen, Mar 3, 2004.

  1. curwen

    curwen Guest

    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
    curwen, Mar 3, 2004
    #1
    1. Advertising

  2. curwen

    Angus Guest

    curwen wrote:
    >
    > 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.
    Angus, Mar 4, 2004
    #2
    1. Advertising

  3. curwen

    Angus Guest

    Angus wrote:
    >
    > 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.
    Angus, Mar 4, 2004
    #3
  4. curwen

    RJG Guest

    (curwen) wrote in message news:<>...
    > 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-
    RJG, Mar 5, 2004
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. [XaToA]
    Replies:
    1
    Views:
    421
    Roedy Green
    Jul 30, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,251
    Michael Borgwardt
    May 30, 2004
  3. Michael Hertz
    Replies:
    3
    Views:
    10,220
    Martin Honnen
    Oct 4, 2004
  4. pythos
    Replies:
    1
    Views:
    455
    Peter Hansen
    Aug 20, 2004
  5. praba kar
    Replies:
    1
    Views:
    395
    Max M
    Apr 8, 2005
Loading...

Share This Page