Weird behaviour encountered using java.sql.TimeStamp and a mysql database

Question

The weird behavior is that a java.sql.Timestamp that I create using the System.currentTimeMillis() method, is stored in my MySQL database as 1970-01-01 01:00:00.

The two timestamps I am creating are to mark the beginning and end of a monitoring task I am trying to perform, what follows are excepts from the code where the behavior occurs

final long startTime = System.currentTimeMillis();
while(numberOfTimeStepsPassed < numTimeStep) {
/*
* Code in here 
*/
}
final long endTime = System.currentTimeMillis();
return mysqlConnection.insertDataInformation(matrixOfRawData, name,Long.toString(startTime), 
                                                 Long.toString(endTime), Integer.toString(numTimeStep),
                                                 Integer.toString(matrixOfRawData[0].length), owner,
                                                 type);

And here is the code used for inserting the time stamps and other data into the MySQL database

public String insertDataInformation(final  double [][] matrix,
                                    final String ... params) {
    getConnection(lookUpName);
    String id = "";
    PreparedStatement dataInformationInsert = null;
    try {
        dataInformationInsert =
                databaseConnection.prepareStatement(DATA_INFORMATION_PREPARED_STATEMENT);
        id = DatabaseUtils.createUniqueId();
        int stepsMonitored = Integer.parseInt(params[STEPS_MONITORED]);
        int numberOfMarkets = Integer.parseInt(params[NUMBER_OF_MARKETS]);
        dataInformationInsert.setNString(ID_INDEX, id);
        dataInformationInsert.setNString(NAME_INDEX, params[0]);
        dataInformationInsert.setTimestamp(START_INDEX, new Timestamp(Long.parseLong(params[START_INDEX])));
        dataInformationInsert.setTimestamp(END_INDEX, new Timestamp(Long.parseLong(params[END_INDEX])));
        dataInformationInsert.setInt(STEPS_INDEX, stepsMonitored);
        dataInformationInsert.setInt(MARKETS_INDEX, numberOfMarkets);
        dataInformationInsert.setNString(OWNER_INDEX, params[OWNER]);
        dataInformationInsert.setNString(TYPE_INDEX, params[TYPE]);
        dataInformationInsert.executeUpdate();
        insertRawMatrix(matrix, id, Integer.toString(stepsMonitored), Integer.toString(numberOfMarkets));
    } catch (SQLException sqple) {
        // TODO Auto-generated catch block
        sqple.printStackTrace();
        System.out.println(sqple.getSQLState());
    } finally {
        close(dataInformationInsert);
        dataInformationInsert = null;
        close(databaseConnection);
    }
    return id;
}

The important lines of code are :

dataInformationInsert.setTimestamp(START_INDEX, new Timestamp(Long.parseLong(params[START_INDEX])));
dataInformationInsert.setTimestamp(END_INDEX, new Timestamp(Long.parseLong(params[END_INDEX])));

The JavaDocs on the TimeStamp ( http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/Timestamp.html ) says that it takes in time in milliseconds since 1st January 1970 and a simple print test confirms this.

What I am looking for is:

A reason for this behavior when trying to store timestamps in a MySQL database through java.sql.Timestamp?
Any solutions to this behavior?
Any possible alternatives?
Any possible improvements?

EDIT: Been asked to include what START_INDEX and END_INDEX are:

 private static final int END_INDEX = 4;
 private static final int START_INDEX = 3;

Apologises for not putting them in the original post.

Why are you converting the value to a string and back? Why not just take each parameter in a strongly-typed way? — Jon Skeet
– Jon Skeet, Commented Mar 27, 2012 at 15:13
When I was originally writing the code I was unsure how many parameters the method would need, so I decided to make use of the String ... args to save myself some time at the time :) — OpelMac
– OpelMac, Commented Mar 27, 2012 at 15:21
I strongly suggest you change that right away. You can always use a separate "builder" for the parameters, so that you don't rely on ordering. Currently we can't see what START_INDEX etc are, so the problem could be there... we can't tell. — Jon Skeet
– Jon Skeet, Commented Mar 27, 2012 at 15:22
Just added what START_INDEX and END_INDEX are defined as in an edit to my original post. — OpelMac
– OpelMac, Commented Mar 27, 2012 at 15:26
Also just after re reading what you have stated there, I would like to clarify that I am getting no SQL exception being thrown. The data does get inserted into the database, just in the case of the time stamps they appear as I stated above, 1970-01-01 01:01:00 — OpelMac
– OpelMac, Commented Mar 27, 2012 at 15:38

Jon Skeet · Accepted Answer · 2012-03-27 15:39:16Z

1

Okay, look at your call:

insertDataInformation(matrixOfRawData, name, Long.toString(startTime), 
                      Long.toString(endTime), Integer.toString(numTimeStep),
                      Integer.toString(matrixOfRawData[0].length), owner,
                      type);

So params will have values:

0: name
1: start time
2: end time
3: numTimeStep
4: matrixOfRowData[0].length
5: owner
6: type

Then you're doing:

dataInformationInsert.setTimestamp(START_INDEX,
    new Timestamp(Long.parseLong(params[START_INDEX])));

... where START_INDEX is 3.

So you're using the value corresponding to numTimeStep as the value for the timestamp... I suspect you don't want to do that.

I would strongly advise you to create a simple object type (possibly a nested type in the same class) to let you pass these parameters in a strongly typed, simple to get right fashion. The string conversion and the access by index are both unwarranted, and can easily give rise to errors.

answered Mar 27, 2012 at 15:39

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

5 Comments

OpelMac Over a year ago

That would be correct but those params values do not match up directly with the above statements. For a number of reasons the name index isn't the first parameter set, the id is (created inside the method) and the JDBC seems to start indexing parameters at 1 not at 0. Which is strange I know. I am sure of these because a mysql exception would be thrown if the ordering of my parameters was wrong. If there is a plus side you have convinced me to change the parameters to strongly typed. I forgot my code has to be readable to others not just me!

Jon Skeet Over a year ago

@OpelMac: I have no idea what you mean, but the code you've posted has the problem I've described. If your actual code is significantly different, then I'm not sure how I'm meant to be able to help you...

OpelMac Over a year ago

I accidently hit enter earlier on before finishing my comment. I apologise for that :)

Jon Skeet Over a year ago

@OpelMac: I'm not talking about the JDBC indexing - I'm talking about the array indexing. You are using new Timestamp(Long.parseLong(params[START_INDEX]))); which is fetching params[3]. Unless that actually corresponds to your start timestamp, that's a very real problem. (Getting the right JDBC parameter index is another challenge, but we can't see your SQL so we can't tell if that bit is right.)

OpelMac Over a year ago

Sorry about that. You are 200% right! If I knew who you were I would buy you a drink for this! Thanks a million man. You deserve all those medals!

Collectives™ on Stack Overflow

Weird behaviour encountered using java.sql.TimeStamp and a mysql database

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related