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

Question

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()+"')");

I actualy want to run a query on my table for example Select * from xyz where datetime between 13:00 to 18:00 .. how would that work with a string object if i had the column data type as VarChar? — dev_marshell08, Feb 21 '14 at 01:55
Before you commit your first design and solution, perhaps you'd like to read about some of the pitfalls. Here's a pretty good [starting point on SO](http://stackoverflow.com/questions/9202857/timezones-in-sql-date-vs-java-sql-date). — Paul Hicks, Feb 21 '14 at 01:56
You should certainly be using a PreparedStatement with arguments instead of constructing the SQL like that. — user207421, Feb 21 '14 at 04:25

Wasif Hossain · Answer 1 · 2014-02-21T02:49:05.127

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

Tried that but it would still insert 6828-00-00 00:00:00' in MySql — dev_marshell08, Feb 21 '14 at 02:18

score 0 · Answer 2 · answered Feb 21 '14 at 02:05

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)

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

2 Answers2

EDIT