10

I have a JDBC current date and time insert into Mysql Database.

It submits the current date and a fixed time into the Mysql date type field as 2012/05/25 00:00:00. The date part works very fine but the time doesn't shows any value.

I'm inserting the value with the following code:

java.util.Date myDate = new java.util.Date();
java.sql.Date sqlDate = new java.sql.Date(myDate.getTime());
PreparedStatement PStmt = con.prepareStatement(Sql query);
PStmt.setDate(1,sqlDate);
Andrzej Doyle
  • 102,507
  • 33
  • 189
  • 228
Narayan
  • 1,189
  • 6
  • 15
  • 33

5 Answers5

20

Use java.sql.Timestamp instead of java.util.Date

For example if you are using PreparedStatement to handle your insert, you can pass your date as;

java.util.Date date = new Date();
pst.setTimestamp(columIndex, new java.sql.Timestamp(date.getTime()));
Bitmap
  • 12,402
  • 16
  • 64
  • 91
  • 1
    +1: The problem is that `java.sql.Date` is *specifically* only a date, and so discards the time component. – Andrzej Doyle May 25 '12 at 10:51
  • It shows error when i tried to execute the insert statement ie, it shows error when i use pstmt.executeupdate() ; – Narayan May 25 '12 at 11:00
  • You've not specified the error you're getting, I assume it is asking for the long as the parameter - see edited answer. – Bitmap May 25 '12 at 11:05
  • debug your time passed using `System.out.println(new java.sql.Timestamp(date.getTime()));` if this gives you the correct value, then assume the issue is the datatype held on the database table to hold `datetime`. – Bitmap May 25 '12 at 11:19
  • Should the last `.getTime()` be there in `new java.sql.Timestamp(date.getTime()).getTime()`? – Max Nanasy Jan 15 '14 at 23:05
  • Unless I'm missing something, `PreparedStatement` doesn't have a method `setTimestamp(int, long)`, but it does have `setTimestamp(int, Timestamp)`, so shouldn't it be `pst.setTimestamp(columIndex, new java.sql.Timestamp(date.getTime()))`? – Max Nanasy Jan 16 '14 at 21:16
  • `pst.setTimestamp(columIndex, new java.sql.Timestamp(date.getTime()))` Is the correct assignment. I have corrected the typo. Ta! – Bitmap Jan 17 '14 at 09:52
2

I think you need to have timestamp as column type in mysql.

Subir Kumar Sao
  • 8,171
  • 3
  • 26
  • 47
2

I suggest to note the distinction between java.util.Date and java.sql.Date

java.util.Date vs java.sql.Date

EDIT also make sure you have the proper data type in the database (DATE, DATETIME, or TIMESTAMP)

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

Community
  • 1
  • 1
bpgergo
  • 15,669
  • 5
  • 44
  • 68
1

1) Use java.util.Date in your java class.

2) Set datatye TIMESTAMP in mysql.

Date CreatedDate= new Date(System.currentTimeMillis());

For PreparedStatement: It may be like this:

PreparedStatement PStmt = con.prepareStatement(Sql query);
PStmt.setDate(1,CreatedDate);
Sachin J
  • 2,081
  • 12
  • 36
  • 50
1

Although an old post, the answer is simple and depends on your requirement - as per your requirements use DATETIME data type and use the following

java.util.Date date = new Date();
pst.setTimestamp(columIndex, new java.sql.Timestamp(date.getTime()).getTime());

Let's see the reasoning from both the database and jdbc side.

Start from the data type in the database and pick appropriate one. The one which exactly suits your case is DATETIME as you want date and hour/min/sec. DATETIME type is used for storing wall clock time whereas TIMESTAMP is for a fixed point in time (some milliseconds after epoch). There is also difference in how both are stored internally in MySQL.

Now When you come to the JDBC side you have API methods for Date ( only date part), Timestamp (date and time) and Time (only time). That's all what the JDBC has to offer. So now, the API which suits your need is setTimestamp

Shailendra
  • 8,874
  • 2
  • 28
  • 37