Compare two dates in Java received from MySQL
I need to compare two dates in Java, but the date retrieved from the database is not the same as the value that was inserted.
@Test
public void testCompareHoraHardcore() {
String sql = "update solicitacao_viagem set "
+ "hora_retorno=?, id_veiculo=?, id_responsavel_solicitacao=?, "
+ "id_responsavel_autorizante=? where id_solicitacao_viagem=? ";
Timestamp timestamp1 = new Timestamp(System.currentTimeMillis());
try {
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setTimestamp(1, timestamp1);
stmt.setInt(2, 90);
stmt.setInt(3, 337);
stmt.setInt(4, 337);
stmt.setInt(5, 91);
stmt.executeUpdate();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
Timestamp timestamp2 = null;
try {
sql = "select * from solicitacao_viagem where id_solicitacao_viagem=?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setInt(1, 91);
stmt.executeQuery();
ResultSet rs = stmt.executeQuery();
rs.next();
timestamp2 = rs.getTimestamp("hora_retorno");
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
assertEquals(timestamp1.getTime(), timestamp2.getTime());
}
Returning this test:
Testcase: testCompareHoraHardcore(com.model.dao.SolicitacaoViagemDAOTest): FAILED
expected:<1360553289573> but was:<1360553289000>
junit.framework.AssertionFailedError: expected:<1360553289573> but was:<1360553289000>
at com.model.dao.SolicitacaoViagemDAOTest.testCompareHoraHardcore(SolicitacaoViagemDAOTest.java:349)
Why is MySQL abbreviating the last three numbers?
I am using MySQL 5.5.22 and Java (TM) SE Runtime Environment (build 1.7.0_11-b21).
The "hora_retorno" column is TIMESTAMP.
Thanks in advance.
+3
source to share
2 answers
Accordingly, MySQL does not store fractional seconds in Timestamp columns (or other types of date and time). http://dev.mysql.com/doc/refman/5.0/en/fractional-seconds.html
+2
source to share
MySql saves DATETIME
and TIMESTAMP
up to seconds . Here is a quote from the documentation
A DATETIME or TIMESTAMP value can include a trailing fractional seconds
part in up to microseconds(6 digits) precision.
Although this fractional part is recognized,
it is discarded from values stored into DATETIME or TIMESTAMP columns
+3
source to share