How to Store Java Date to MySQL Datetime with JPA

How to store Java Date to Mysql datetime with JPA

see in the link :

http://www.coderanch.com/t/304851/JDBC/java/Java-date-MySQL-date-conversion

The following code just solved the problem:

java.util.Date dt = new java.util.Date();

java.text.SimpleDateFormat sdf = 
     new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String currentTime = sdf.format(dt);

This 'currentTime' was inserted into the column whose type was DateTime and it was successful.

Spring JPA java.util.Date to Mysql timestamp with wrong date value

Found the issue, the problem was in jdbc connection URL.

spring.datasource.url=jdbc:mysql://:3307/dbname?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

due to the server time zone setting in the URL, GMT+8 time was converted to UTC which was leading to the 8 hours time difference. After removing the serverTimezone parameter in the URL, date is working fine. Thank you Neil and Shazin for your time and support.

How to store mysql date in Java , which type of variable to use?

java.time.LocalDate

MySQL's DATE type is used for values with a date part but no time part, so you're better off to use the LocalDate API.

Another variant is the LocalDateTime API. Use this if you want to represent a date as well as a time i.e. MySQL's DATETIME type would require LocalDateTime object to represent it on the client side (Java program).

As an aside, avoid exposing fields to the outside world. make them all private to enforce encapsulation and only provide getters and setters where required.

Unable to compare java.util.Date value with MySQL datetime column values

I am not sure exactly what you expected as your question is unclear, so I cannot give a precise answer. But I can discuss some of the problems in your approach and your code.

Firstly, never use java.util.Date. That terrible class is part of the legacy date-time classes that were supplanted years ago by the modern java.time classes defined in JSR 310. Likewise, never use Calendar, and never use SimpleDateFormat. Sun, Oracle, and the JCP community gave up on those legacy classes upon the adoption of JSR 310, and so should you. JPA and Hibernate have both been updated to support java.time classes, as has the JDBC 4.2 specification.

Another possible problem, perhaps you are mixing up java.sql.Date with java.util.Date. Be careful of your import statements there. And, again, never use either of the Date classes.

You mention a string of 2021-03-03, but don't really explain where that is. At any rate, that represents a date-only value. But you say you accessing a DATETIME column in MySQL. According the the MySQL 8 documentation, that type represents a date with a time-of-day.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

So querying a date-with-time column by a date-only value makes no sense. The database cannot compare a date-only value to a date-with-time. You need to specify a span of time, a pair of date-with-time values for your start and end.

Another problem: You are trying to use java.util.Date which represents a moment, a specific date with time as seen in the context of UTC — but the DATETIME type in MySQL is a different animal. The DATETIME type in MySQL represents a date with a time-of-day but lacks the context of an offset or time zone. This type is akin to the SQL type TIMESTAMP WITHOUT TIME ZONE.

Beware: If you are trying to represent moments in your database, specific points on the timeline, then you are using the wrong data type for your column. For moments, you must use a data type akin to the SQL-standard TIMESTAMP WITH TIME ZONE. In MySQL 8, that would be the TIMESTAMP type. The TIMESTAMP type in MySQL tracks moments as seen in UTC, always in UTC. If your column in question is the one named createdAt, then you almost certainly are using the wrong data type, as tracking moments that passed as history must be done with SQL-standard TIMESTAMP WITH TIME ZONE. But I will ignore this crucial issue, and carry on with regard to the type you specified in your Question as written.

If you are not tracking moments, such as tracking future appointments alongside time zone saved in an additional column, then your use of DATETIME would be correct. If you are sure of this datatype for your purpose, then in Java the appropriate match would be LocalDateTime class.

Do your query for a day's worth of data over a DATETIME column by using the half-open approach to defining a span of time. The start is inclusive while the end is exclusive. So a day starts at first moment of the day and runs up to, but does not include, the start of the next day. With half-open, we do not use the SQL keyword BETWEEN.

Here is a simple code example to adapt to your situation. I use neither Spring nor Hibernate, so you will have to translate accordingly.

For your date-only input string, parse as a LocalDate.

Tip: A short way of asking "Is greater than or equal" is "Is not less than". We see this on the first case of the predicate in the SELECT statement below.

LocalDate ld = LocalDate.parse( "2021-03-03" ) ;
LocalDateTime startOfDay = ld.atStartOfDay() ;
LocalDateTime startOfNextDay = ld.plusDays( 1 ).atStartOfDay() ;
String sql = "SELECT * FROM my_table WHERE when !< ? AND when < ? ;" ;  // Performing query over a span-of-time defined as Half-Open. 
…
myPreparedStatement.setObject( 1 , startOfDay ) ;
myPreparedStatement.setObject( 2 , startOfNextDay ) ;

Retrieving DATETIME values.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

All this has been discussed many many times already on Stack Overflow. Search to learn more.

MySQL Date Changes to Yesterday's date After JPA Save

I think its because of Timezone problem. Locale Date do not consider the timezone as you already know. But In the database, I guess the date is associated with Timezone. The JPA/Hibernate layer will convert the LocaleDate to TimeStamp( default it will take JVM timezone during the conversion ). The timezone that your running the application is different from the database timezone, due to this there is mismatch.

To confirm on this, set the timezone of the running machine to UTC.

---

Related Topics