How to store Java Date to Mysql datetime with JPA

Question

Can any body tell me how can I store Java Date to Mysql datetime...?

When I am trying to do so...only date is stored and time remain 00:00:00 in Mysql date stores like this...

2009-09-22 00:00:00

I want not only date but also time...like

2009-09-22 08:08:11

I am using JPA(Hibernate) with spring mydomain classes uses java.util.Date but i have created tables using handwritten queries...

this is my create statement

CREATE TABLE ContactUs (
  id BIGINT AUTO_INCREMENT, 
  userName VARCHAR(30), 
  email VARCHAR(50),
  subject VARCHAR(100), 
  message VARCHAR(1024), 
  messageType VARCHAR(15), 
  contactUsTime DATETIME,
  PRIMARY KEY(id)
);

link : http://www.coderanch.com/t/304851/JDBC/java/Java-date-MySQL-date-conversion — Haim Evgi, Mar 08 '10 at 11:39
These days you should no longer be using `java.util.Date` at all (in 2010 there probably was no better choice). Better to use the modern classes, like `java.time.Instant` or `java.time.LocalDateTime`, depending on the exact requirements for the date-time you need to store. — Ole V.V., Aug 13 '17 at 06:26
Some of these old answers comeup pretty high on Google rankings. In Java 8+ we can simply use LocalDateTime to save datetime instead of resorting to using @Temporal to save in datetime format — HopeKing, Aug 31 '17 at 03:32
preparedStatement.setTimestamp(1, new Timestamp(System.currentTimeMillis())); — Rajat, Dec 12 '18 at 05:58

score 170 · Accepted Answer · edited Nov 19 '11 at 22:33

170

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.

edited Nov 19 '11 at 22:33

RealHowTo

34,977
11
70
85

answered Mar 08 '10 at 11:40

Haim Evgi

123,187
45
217
223

6

but,currentTime is a String object, and in db we take datetime as datatype,i think it is not going to be inserted.right? – reddy May 08 '14 at 11:44
`yyyy-MM-dd HH:mm:ss` is very important. Take note of the capitalization. – biniam Dec 31 '15 at 15:34

score 96 · Answer 2 · answered Mar 09 '10 at 16:43

96

Annotate your field (or getter) with @Temporal(TemporalType.TIMESTAMP), like this:

public class MyEntity {
    ...
    @Temporal(TemporalType.TIMESTAMP)
    private java.util.Date myDate;
    ...
}

That should do the trick.

answered Mar 09 '10 at 16:43

Pascal Thivent

562,542
136
1,062
1,124

2

This is the answer indeed! – Perneel Jul 09 '13 at 09:00
2

up for this, this is the proper answer. – Ryan Nov 18 '14 at 08:00
4

be aware that if you choose this solution, your date will appear as a timestamp when fetched from db. It won't have the nice formatting 'yyyy-MM-dd....') – jon Aug 05 '16 at 18:52

score 38 · Answer 3 · edited Mar 09 '10 at 16:38

38

Are you perhaps using java.sql.Date? While that has millisecond granularity as a Java class (it is a subclass of java.util.Date, bad design decision), it will be interpreted by the JDBC driver as a date without a time component. You have to use java.sql.Timestamp instead.

edited Mar 09 '10 at 16:38

Pascal Thivent

562,542
136
1,062
1,124

answered Mar 08 '10 at 11:43

Michael Borgwardt

342,105
78
482
720

1

I wouldn't use `java.sql.Date` or `java.sql.Timestamp` in domain classes but I guess this was written before the edit of the OP. – Pascal Thivent Mar 09 '10 at 16:38
I would. java.util.Date objects don't compare to java.sql.Timestamp objects with entirely predictable results. – Doug Moscrop Aug 05 '11 at 21:28

score 7 · Answer 4 · answered Mar 08 '10 at 11:43

7

Probably because your java date has a different format from mysql format (YYYY-MM-DD HH:MM:SS)

do this

 DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
 Date date = new Date();
 System.out.println(dateFormat.format(date));

answered Mar 08 '10 at 11:43

Marcx

6,806
5
46
69

You mentioned the correct format in the reference and then used the wrong format in the code. – Merric Huffstutler Jan 14 '20 at 15:02

score 6 · Answer 5 · edited Nov 19 '11 at 22:24

6

you will get 2011-07-18 + time format

long timeNow = Calendar.getInstance().getTimeInMillis();
java.sql.Timestamp ts = new java.sql.Timestamp(timeNow);
...
preparedStatement.setTimestamp(TIME_COL_INDEX, ts);

edited Nov 19 '11 at 22:24

animuson

53,861
28
137
147

answered Mar 08 '11 at 13:33

Anuj

61
1
1

score 5 · Answer 6 · answered Dec 25 '12 at 13:18

mysql datetime -> GregorianCalendar

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = format.parse("2012-12-13 14:54:30"); // mysql datetime format
GregorianCalendar calendar = new GregorianCalendar();
calendar.setTime(date);
System.out.println(calendar.getTime());

GregorianCalendar -> mysql datetime

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String string = format.format(calendar.getTime());
System.out.println(string);

score 4 · Answer 7 · answered Jun 11 '13 at 08:48

4

java.util.Date date = new Date();
Object param = new java.sql.Timestamp(date.getTime());    
preparedStatement.setObject(param);

answered Jun 11 '13 at 08:48

michdraft

556
3
11
31

score 1 · Answer 8 · answered Mar 12 '14 at 05:08

Use the following code to insert the date into MySQL. Instead of changing our date's format to meet MySql's requirement, we can help data base to recognize our date by setting the STR_TO_DATE(?, '%l:%i %p') parameters.

For example, 2014-03-12 can be represented as STR_TO_DATE('2014-03-12', '%Y-%m-%d')

preparedStatement = connect.prepareStatement("INSERT INTO test.msft VALUES (default, STR_TO_DATE( ?, '%m/%d/%Y'), STR_TO_DATE(?, '%l:%i %p'),?,?,?,?,?)");

score 1 · Answer 9 · answered May 15 '17 at 18:29

Its very simple though conditions in this answer are in mysql the column datatype is datetime and you want to send data from java code to mysql:

java.util.Date dt = new java.util.Date();
whatever your code object may be.setDateTime(dt);

important thing is just pick the date and its format is already as per mysql format and send it, no further modifications required.

score 1 · Answer 10 · answered Feb 24 '22 at 03:45

If using java 8 or higher , try to use LocalDateTime. That was the correct type if you are using DATETIME as mysql data type.

Below is example for conver current time to "2009-09-22 08:08:11" format

LocalDateTime currentTime = LocalDateTime.parse(LocalDateTime.now().toString(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

score 0 · Answer 11 · answered Dec 20 '16 at 03:57

Actually you may not use SimpleDateFormat, you can use something like this;

  @JsonSerialize(using=JsonDateSerializer.class)
  @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "dd/MM/yyyy HH:mm:ss")
  private Date blkDate;

This way you can directly get the date with format as specified.

score 0 · Answer 12 · answered Jun 14 '19 at 12:38

0

I still prefer the method in one line

new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime())

answered Jun 14 '19 at 12:38

Mohannd

1,288
21
20

score -2 · Answer 13 · answered Nov 20 '14 at 17:54

-2

it works for me !!

in mysql table

DATETIME

in entity:

private Date startDate;

in process:

objectEntity.setStartDate(new Date());

in preparedStatement:

pstm.setDate(9, new java.sql.Date(objEntity.getStartDate().getTime()));

answered Nov 20 '14 at 17:54

Miguel Alonso Mosquera Aguilar

1

How to store Java Date to Mysql datetime with JPA

13 Answers13

Linked

Related