Timestamp insert issue in Mysql from Oracle

Question

I am trying to insert the timestamp value from oracle to mysql timestamp column. oracle value is 2017-09-01 11:35:22.495000000 but while getting value from result set its giving 2017-09-01 11:35:22.495.

its stored in oracle using oracle.sql.timestamp and i cannot insert the value in mysql.so getting stringvalue or timestamp value from oracle.sql.timestamp API.

But mysql storing the value is 2017-09-01 11:35:22.000495 and datatype defined as timestamp (6) and am not sure why its inserting the value like this?

How i can store the value in mysql similar to oracle ?

Michael Powers · Accepted Answer · 2018-10-24 15:15:00Z

Using JDBC you should be able to directly copy a timestamp from one database to another doing something like this:

try(Connection oracleConnection = getOracleConnection();
    Connection mysqlConnection = getMySQLConnection();
    PreparedStatement oracleStmt = oracleConnection.prepareStatement("SELECT my_time FROM oracle_table");
    PreparedStatement mysqlStmt = mysqlConnection.prepareStatement("INSERT INTO mysql_table VALUES (?)");
    ResultSet rs = oracleStmt.executeQuery()) {

    while(rs.next) {
        mysqlStmt.setTimestamp(1, rs.getTimestamp("my_time"));
        mysqlStmt.execute();
    }
}

Timestamps are essentially numeric datatypes. Different DBMS's can have different precisions and different ways of handling timezones, but you shouldn't need any database specific API's to interact with them in most cases.

If you need to format a Timestamp you can use SimpleDateFormat on what you get back from getTimestamp() to format the string any way you need to.

Collectives™ on Stack Overflow

Timestamp insert issue in Mysql from Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related