Java sql - How to insert TimeStamp into a mysql database including milliseconds

Question

How do I convert a Java TimeStamp object to a SQL datetime type that includes milliseconds? When I insert my TimeStamp obj into the database the milliseconds are gone.

This is my Java source code to translate a String into a Timestamp object:

import java.sql.Timestamp;
...
...
String timeStr = "2013-08-30 19:11:49.113";
Timestamp timeCreated = Timestamp.valueOf(timeStr);
timeCreated.toString(); //returns "2013-08-30 19:11:49.113"

My table dfinition in SQL:

CREATE TABLE `Event` (EventTimeStamp datetime NOT NULL);

This is how I insert the TimeStamp object into my Database:

PreparedStatement psInsert;
Connection conn = ...
psInsert = conn
            .prepareStatement("INSERT INTO `Event` (EventTimeStamp) VALUES(?)");  

psInsert.setTimestamp(1, timeCreated);
psInsert.executeUpdate();

After insertion the TimeStamp is cut and there are no milliseconds anymore:

2013-08-30 19:11:49

Thx

Okay I updated my mysql version and my mysql workbench with this guide: [link] {http://comet.lehman.cuny.edu/jung/cmp420758/guide.pdf} but I did not effect the milliceond problem — Craiten, May 16 '16 at 17:35
strange, can you insert millis in bench and check the result, to test whether this mysql or java one — Iłya Bursov, May 16 '16 at 17:36
The answer of @Rob below and your advice to update my mysql version did the trick. I had to use `datetime(3)` instead of `datetime`. `datetime(3)` did not work with my version 5.5.49 but does work now with the version 5.6.3 . Furthermore, `datetime` only does not fix the milliseconds problem in version 5.6.3 — Craiten, May 17 '16 at 09:15

score 2 · Accepted Answer · answered May 16 '16 at 16:22

2

Your database column datetime has no place for milliseconds. You should use datetime(3) instead.

answered May 16 '16 at 16:22

Rob

6,247
2
25
33

This did the trick! Thx! But I needed to install the latest mysql version for this solution. @Lashane thx for the advice to update my mysql. – Craiten May 16 '16 at 17:39

Java sql - How to insert TimeStamp into a mysql database including milliseconds

1 Answers1