CODEWITHSUNDEEP
javadatetime
dev_marshell08
dev_marshell08

Reputation: 1099

How to store date object into MySql in HH:MM format?

I am trying to create a date object(format : HH:MM) from a String Example 13:30(HH:MM). I want to save the HH:MM in MySql table but the below code enters some random value in the column (eg: '6828-00-00 00:00:00'). How can i store the date value in Mysql in the HH:MM format ? 

Date date = null; 
String afternoon = "13" +":" +"30";
String time = afternoon;


try {                               
 date = new SimpleDateFormat("HH:mm").parse(time);
}
catch (ParseException e) {
    e.printStackTrace();
}

long d = date.getTime();
java.sql.Date sqlDate = new java.sql.Date(d);

 String sql3 = "CREATE TABLE IF NOT EXISTS DateTime"+
                       "(UniqueBusID VARCHAR(255) not NULL, " +
                       " Timenings DATETIME DEFAULT NULL ,"+
                       " PRIMARY KEY ( UniqueBusID ))";

stmt.executeUpdate(sql3);
stmt.executeUpdate("INSERT INTO DateTime " + "VALUES ('Test3','"+sqlDate.getTime()+"')");

Upvotes: 1

Views: 1635

Answers (2)

Wasif Hossain
Wasif Hossain

Reputation: 3950

EDIT

You have to use java.sql.Timestamp instead of java.sql.Date. As from javadoc,

"To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated."

So just replace the line 

java.sql.Date sqlDate = new java.sql.Date(d);

with 

java.sql.Timestamp sqlTime = new Timestamp(d);

You are doing it all right, except the pattern you have used to parse the String as Date i.e. HH:mm, which is wrong.

Correct Format : H:mm (H: 0-23; h:1-12)

try {                               
 date = new SimpleDateFormat("H:mm").parse(time);
}

Here is the reference for Date and Time Patterns

Upvotes: 1

Leo
Leo

Reputation: 6580

I am surprised that it inserted '6828-00-00 00:00:00' :-)

Four problems here.

[1] First that when you set a SimpleDateFormat, you're creating a java object Date. Java Date starts from Jan 1st 1970, so if you set only the hour and minute, the formatter will assume all other fields are zero (and not today), so 

System.out.println(new SimpleDateFormat("HH:mm").parse("13:30")); // returns Thu Jan 01 13:30:00 BRT 1970

[2] But then, you've called the getTime() method, which returns the millis since Jan 1st 1970

System.out.println(new SimpleDateFormat("HH:mm").parse("13:30").getTime()); //59400000

[3] Then, you've tried to push this number into MySQL. MySQL datetime expects a string in the format

YYYY-MM-DD HH:MM:SS

(see https://dev.mysql.com/doc/refman/5.0/en/datetime.html)

Since MySQL is a very benevolent database ;-) it tries to convert 59400000 into this format, which obviously

mysql> insert into d values (59400000); Query OK, 1 row affected (0.04 sec)

mysql> select * from d;
+---------------------+
| y                   |
+---------------------+
| 5940-00-00 00:00:00 |
+---------------------+
1 row in set (0.08 sec)

[4] Of course, you could just adjust your SimpleDateFormat to MySQL expected date format, but you're concatenating strings in a INSERT query, and this is not a good idea for security reasons neither is efficient. Instead, you should use a PreparedStatement and set the Date object (not the millis)

Upvotes: 0

Related Questions