1

I don't know why, but each time I try to save a LocalDateTime to MySQL then later try to view it, 7 hours have been subtracted from it. For example, when I put in LocalDateTime.of(2018, 6, 28, 18, 30) and then save it when I get it back out of the database, it says 2018-06-28 11:30. Now, I get that the reason it is doing this is because I live in Seattle which is in GMT-7. What I don't know is how to get it to stop doing that. I'm guessing this has a very simple solution, but I can't seem to figure it out.

--EDIT--

Here is the code I am using to save to the database.

public static int update(String tableName, String idColumnName, int id, Map<String, Object> newValues) {
    String sql = "UPDATE " + tableName + "\n"
               + "SET lastUpdate = " + convertDateToDBString(LocalDateTime.now()) + ",\n"
               + "lastUpdateBy = '" + activeUser.getUsername() + "'";

    for (Map.Entry<String, Object> newValue : newValues.entrySet()) {
          sql +=  ", \n" + newValue.getKey() + " = " + convertObjectToDBString(newValue.getValue());
    }
          sql += "\nWHERE " + idColumnName + " = " + id + ";";

    if (connected) {
        try {
            printSQL(sql);
            return statement.executeUpdate(sql);
        } catch (SQLException e) {
            printError(1111, "Could not run UPDATE query", e);
        }
    }
    return -1;
}

Here is the code I am using to draw from the database.

public static ResultSet query(String sql) {
    if (connected) {
        try {
            printSQL(sql);
            if (statement.execute(sql)) return statement.getResultSet();
        } catch (SQLException e) {
            printError(1110, "Could not run SELECT query", e);
        }
    }
    return null;
}

I don't think this will really help solve the issue which is why I didn't bother adding it in the first place, but you guys are asking for it.

-- EDIT --

Here is the code for convertDateToDBString.

public static String convertDateToDBString(LocalDateTime datetime) {
    String dateString = "'" + Timestamp.valueOf(datetime) + "'";
    return dateString;
}
ToMakPo
  • 855
  • 7
  • 27
  • 1
    Can you show us the code that is actually inserting/querying the database? – mypetlion Jun 27 '18 at 18:04
  • You are by all probability correct that it’s a time zone issue. Several have struggled with similar before you. So use your search engine first. If what you find doesn’t solve your particular issue, at least edit you question and add the datatype of your MySQL column and [a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve). – Ole V.V. Jun 27 '18 at 18:06
  • I would guess that the code writing to the database is converting it to a UTC timestamp, and the code reading it from the database is using the JVM default timezone. – Andy Turner Jun 27 '18 at 18:09
  • @mypetlion I added the code that I am using to add and query the database. – ToMakPo Jun 27 '18 at 19:16
  • @AndyTurner I have spent the last day and a half looking but I don't know what to even look for. – ToMakPo Jun 27 '18 at 19:17
  • @MakPo what does the code of `convertDateToDBString` look like? – Andy Turner Jun 27 '18 at 19:30
  • Thanks for the code. It’s much more concrete now, easier to relate to. One possible explanation is your MySQL is running UTC and your database session knows that your local machine is running Los Angeles time and therefore subtracts 7 hours in an attempt to translate. If so one possible solution is to use bind variables so the opposite translation would happen when you save the date-time (which I can see from your code that you haven’t done until now). – Ole V.V. Jun 27 '18 at 19:41
  • There’s an example of using a prepared statement and a question mark placeholder/bind variable [in my answer here](https://stackoverflow.com/a/50506259/5772882). I know it doesn’t translate very directly to your situation. I hope you can take some inspiration anyway. With greetings from a former Seattleite. – Ole V.V. Jun 27 '18 at 19:45
  • @AndyTurner I added the `convertDateToDBString` code. – ToMakPo Jun 27 '18 at 20:53
  • 1
    Are you using `DATETIME` or `TIMESTAMP` in MySQL? What is the timezone of the server? Are Java and MySQL aware of the timezone? – Rick James Jun 27 '18 at 20:56
  • 1
    As an aside, used a `PreparedStatement` rather than building your SQL query as a string. – Andy Turner Jun 27 '18 at 20:59
  • In every instance of dealing with dates and time in my program, I am using `java.time`. This is the only time I used 'java.sql' but no mater how I try to convert it, it doesn't fix the issue. Before I was just converting it to a string and it did the same thing. – ToMakPo Jun 27 '18 at 21:01
  • As for the server timezone, I have no idea. This program is a project for school and I have to log into their db which for the project, I have no access to other than via java. – ToMakPo Jun 27 '18 at 21:07

0 Answers0