Inserting a string into a datetime mysql column with java.

Question

Will MySQL allow me to insert a properly formatted string into a datatime field in my MySQL database?

I am parsing a file, combining dates of format yyyyMMdd and times of format HHmmss, and planning to format them into a string like:

     "yyyy-MM-dd HH:mm:ss"

I would like to then insert this string into my datetime field (which has the same formatting as the example above" with a insert query.

If this isn't the proper way to handle my problem, what method should I be using? After looking at the MySQL documentation I got the impression that I could do this as long as the formatting was correct. Working with Java's Calendar and Date classes seemed to only have headaches in store for me.

JB Nizet · Accepted Answer · 2012-06-07 18:23:45Z

2

This isn't the proper way to do that. The proper way is to use a java.sql.Timestamp, and use a prepared statement to insert your date:

Date date = dateFormat.parse(s);
Timestamp timestamp = new Timestamp(date.getTime());
String sql = "insert into my_table (id, instant) values(?, ?)";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setLong(1, id);
stmt.setTimestamp(2, timestamp);
stmt.executeUpdate();

This will make your code portable, not tied to any locale or database.

answered Jun 7, 2012 at 18:23

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eggyal Over a year ago

I had completely overlooked the final paragraph of the question (and its tags)! iFail. +1

Mike Over a year ago

So you recommend this method over what you suggested? @eggyal Is the problem that mine just isn't the proper way to do it?

eggyal · Accepted Answer · 2012-06-07 18:20:42Z

1

As discussed in the manual, there are a number of valid formats for datetime literals of this sort, including that which you propose.

So yes, this will work exactly as you describe. Note however you needn't add the additional formatting, as you could just insert YYYYMMDDHHMMSS directly—it needn't even be quoted as a string.

answered Jun 7, 2012 at 18:20

eggyal

127k18 gold badges221 silver badges245 bronze badges

1 Comment

Mike Over a year ago

Thanks. I hadn't come across that yet. I had been reading this

Andy Jones · Accepted Answer · 2012-06-07 18:23:54Z

0

Yes that would work. MySQL uses the format yyyy-MM-dd HH:mm:ss as its default, but as mentioned, you can use a number of other formats.

If you'd like to check for a valid date, try to perform a idempotent operation on the date.

SELECT 'mydate' + INTERVAL 0 DAY

If MySQL returns the same thing back to you, the date is valid, otherwise MySQL will return NULL.

answered Jun 7, 2012 at 18:23

Andy Jones

6,2804 gold badges33 silver badges47 bronze badges

Comments

bluedevil2k · Accepted Answer · 2012-06-07 18:25:20Z

0

You can use a String if it's formatted correctly. I do it in my code and have a TimeUtil class that converts longs to the prescribed format for the DB.

I can then simply use the statement like

stat.setString(10, TimeUtil.formatTime(message.submitTime));

answered Jun 7, 2012 at 18:25

bluedevil2k

9,5018 gold badges45 silver badges59 bronze badges

Collectives™ on Stack Overflow

Inserting a string into a datetime mysql column with java.

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related