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

Question

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);
}

can you please add more code, i can bet you store timestamp in a long or a double in some moment, then you lose precision.... — Jordi Castilla
– Jordi Castilla, Commented Oct 15, 2015 at 9:01
@Tunaki I'm using MySQL 5.6.26 as I require at least 5.6.4 to store fractional seconds. — SEG.Veenstra
– SEG.Veenstra, Commented Oct 15, 2015 at 9:15
@Jordi Castilla I'm not storing the data by code yet, I've inserted it manually through PHPmyAdmin — SEG.Veenstra
– SEG.Veenstra, Commented Oct 15, 2015 at 9:15
as per your last comment: You are probable right as getTimestamp().getNano() return the numbers I'm looking for. So somehow Java takes the fractional part and turns them into nanoseconds., please paste your java code that retrieves and converts from sql timestamp to java Date.... — Jordi Castilla
– Jordi Castilla, Commented Oct 15, 2015 at 10:55

user5449350 · Accepted Answer · 2015-10-15 11:25:23Z

1

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));

answered Oct 15, 2015 at 11:25

user5449350

111 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

SEG.Veenstra Over a year ago

This looks indeed like a solid workaround for my problem. But then I could also just go and store the UNIX_TIMESTAMP I guess...

SEG.Veenstra · Accepted Answer · 2015-10-15 12:27:44Z

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:

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

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();

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related