CODEWITHSUNDEEP
javamysqldatabasetimestamp
SEG.Veenstra
SEG.Veenstra

Reputation: 728

How do I retrieve the right timestamp from a MySQL database in Java?

I'm storing measurements in a MySQL database. The measurements contain a timestamp field which stores timestamps in the following format:

2015-10-10 10:10:10.11 (so with two digits of milliseconds)

In my Java code I retrieve this value with:

resultSet.getTimestamp(id)

When I print this value it gives me:

2015-10-10 11:11:11.000000011

So I'm trying to figure out WHY it behaves like this and HOW I should solve this issue so I get the right value?

EDIT:

The values show correctly inside the database itself when using select * from measurement

My guess it should be somewhere in the way it is retrieved by Java / JDBC. Both getTimestamp and getString give me the same result.

EDIT 2:

resultSet = statement.executeQuery("select * from measurement");

Measurement m;

while(resultSet.next()) {
    m = new Measurement(
        resultSet.getString(1), 
        resultSet.getString(2), 
        resultSet.getDouble(4), 
        resultSet.getTimestamp(3));
    System.out.println(m);
}

Upvotes: 0

Views: 920

Answers (2)

SEG.Veenstra
SEG.Veenstra

Reputation: 728

I've been looking into the implementation. Even though it's a bit over my head I found out some 'interesting' information.

The implementation of com.mysql.jdbc.ResultSet.getTimestamp() has some clues:

int year = 0;
int month = 0;
int day = 0;
int hour = 0;
int minutes = 0;
int seconds = 0;
int nanos = 0; <<< CLUE

and

if (numDigits < 9) {
    int factor = (int) (Math.pow(10, 9 - numDigits));
    nanos = nanos * factor;
}

and

TimeUtil.fastTimestampCreate(tz, year, month, day, hour, minutes, seconds, nanos);

So it seems to parse everything after the '.' as nanoseconds ending up with leading zeroes. so .11 becomes .000000011.

So I think this explains the WHY it is happening...

The HOW to fix this could be in many ways, some being:

  1. Getting the UNIX_TIMESTAMP as user5449350's answer.
  2. Storing UNIX_TIMESTAMPS instead of TIMESTAMP / DATETIME

  3. Correct the faulty Date object and correct it using Calendar

    Date t = resultSet.getTimestamp(3);
Calendar c = Calendar.getInstance();
c.setTime(t);
c.set(Calendar.MILLISECOND, ((Timestamp)t).getNanos());
t = c.getTime();

Upvotes: 0

user5449350
user5449350

Reputation: 11

I've never been a fan of how MySQL's JDBC driver deals with the TIMESTAMP type. I'd use:

select ROUND(UNIX_TIMESTAMP(col1)*1000) FROM measurement

And then read it with

java.util.Date d = new java.util.Date(resultSet.getLong(1));

Upvotes: 1

Related Questions