0

I'm having a problem with Java console and workbench. Let's start saying I'm using the sql.Date format in java console, and DATE format in the Workbench. I have windows 10 Pro on my laptop, using IntelliJ 2022.3.2 Community edition as IDE, MYSQL with workbench 8.0.32 community edition and mysql-connector-j-8.0.31. From Java console I want to update my birthdate which is already store in the database. So I take in input the date 1994-06-10, prepare the connection, prepare the query.. at the moment that is getting the query, the value is correct (1994-06-10) but if fills the '?' of my query with 1994-06-09, one day less. picture here I printed to make sure the data was stored right, and as you can see it's 1994-06-10, but still the query get filled with one day less. I tried to set the timezone but I think it is right. Do you guys know how can I solve this issue?

I saw in another topic they said it was fault of the mysql connector older version, so I tried to download the latest and still get the same error.

Shadow
  • 33,525
  • 10
  • 51
  • 64
ShadyRaso
  • 25
  • 4
  • Your console has a different timezone then the database. Set the timezone of your database correct in the Intelliji database connection properties – Thallius Feb 12 '23 at 15:36
  • Don’t use `java.sql.Date` in Java. That class is poorly designed, a true hack on top of the already poorly designed `java.util.Date` class. No one should work with any of them any more. Use `LocalDate`, from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/index.html). See [Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2](https://stackoverflow.com/questions/43039614/insert-fetch-java-time-localdate-objects-to-from-an-sql-database-such-as-h2). – Ole V.V. Feb 12 '23 at 15:40
  • @Thallius is correct that this is a time zone issue. Using `LocalDate` is not only the simple but also the efficient way to avoid that issue. – Ole V.V. Feb 12 '23 at 15:41
  • But is it gonna work well LocalDate with MYSQL Workbench? Before trying that I just want to be sure, because I have to change many things on the code to try that. If it exists a quick fix with java.sql.Date would be better – ShadyRaso Feb 12 '23 at 15:44
  • I think I disagree. Doing a fragile quick fix to make your code using a bad old class work for now with the risk that it breaks again when someone tampers with some setting would not be worthwhile in my project, and I doubt it would be in yours. You need not change all your code at once since conversion bewteen the two types exist. – Ole V.V. Feb 12 '23 at 15:48
  • what is the equivalent of .getDate with LocalDate? Because I can't find .getLocalDate and I need that method to make all the changes – ShadyRaso Feb 12 '23 at 15:50
  • [Please see the link that I already gave you.](https://stackoverflow.com/questions/43039614/insert-fetch-java-time-localdate-objects-to-from-an-sql-database-such-as-h2) – Ole V.V. Feb 12 '23 at 15:51
  • I switched everything to LocalDate and still getting the same error :/ – ShadyRaso Feb 12 '23 at 16:11
  • Can anyone tell me what it should help to use another date class? When your client timezone is not matching the database server timezone you can use whatever date class you want you will always have a mismatch in what is shown in your database management tool and what is shown in your applications variable when you debug. – Thallius Feb 12 '23 at 16:53
  • *and still getting the same error :/* That’s bad and unexpected, sorry. @Thallius `LocalDate` unlike `sql.Date` is free from dependency on time zone so ought to work no matter which time zones are configured where. As long as the SQL data type is `date` and there isn’t an error in the driver. – Ole V.V. Feb 12 '23 at 17:02
  • I'm not sure its about the client timezone.. Workbench timezone seems fine, I checked it, and IntelliJ as well, I even tried a print of LocalDateTime.now() and it's my timezone.. I mean even if it was about the timezone, it's 6:17 pm here in italy, there isnt any timezone which is 18 hours before which could give me this error to save -1 day – ShadyRaso Feb 12 '23 at 17:17
  • @ole this is wrong. Database is always storing date + time (even if you use date as column type) in UTC. So if your client is CET (UTC +1 at the moment) the stored date will be one day before if you use time 00:00:00. So if you check the date in database with management tool timezone set to UTC (which is standard) you will see the wrong date – Thallius Feb 12 '23 at 17:18
  • @Thallius Do we agree that we are talking MySQL? [*The `DATE` type is used for values with a date part but no time part.*](https://dev.mysql.com/doc/refman/8.0/en/datetime.html) (I know that for other engines, for example Oracle, it’s a different story.) – Ole V.V. Feb 12 '23 at 17:27
  • Yes, I'm using MY SQL with Workbench and using the DATE time. In java tried both SQL Date and LocalDate and still the same issue. – ShadyRaso Feb 13 '23 at 09:05

0 Answers0