CODEWITHSUNDEEP
javamysqldatetimejpa
Urvish
Urvish

Reputation: 1776

How to store Java Date to Mysql datetime with JPA

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)
);

Upvotes: 116

Views: 326789

Answers (13)

udith
udith

Reputation: 302

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"));

Upvotes: 1

Mohannd
Mohannd

Reputation: 1438

I still prefer the method in one line 

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

Upvotes: 0

Yash Agrawal
Yash Agrawal

Reputation: 464

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.

Upvotes: 1

Sahin Yanlık
Sahin Yanlık

Reputation: 1201

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.

Upvotes: 0

Miguel Alonso Mosquera Aguilar
Miguel Alonso Mosquera Aguilar

Reputation: 1

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()));

Upvotes: -2

Yang_2333
Yang_2333

Reputation: 714

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'),?,?,?,?,?)");

Upvotes: 1

michdraft
michdraft

Reputation: 556

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

Upvotes: 4

Nikopol
Nikopol

Reputation: 1141

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);

Upvotes: 5

Haim Evgi
Haim Evgi

Reputation: 125624

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.

Upvotes: 170

Anuj
Anuj

Reputation: 61

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);

Upvotes: 6

Pascal Thivent
Pascal Thivent

Reputation: 570615

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.

Upvotes: 97

Michael Borgwardt
Michael Borgwardt

Reputation: 346536

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.

Upvotes: 38

Marcx
Marcx

Reputation: 6836

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));

Upvotes: 7

Related Questions