How to insert a microsecond-precision datetime into mysql？

Question

I have a string like this:

2011-11-11 11:11:11.111111

and I need to insert it in MySql, into a datetime column. But after I insert it, it becomes

2011-11-11 11:11:11

What's going wrong?

Answer 1

MySql 5.6+ supports fractional seconds in Time Values, while previous versions don't.

A standard datetime column will not hold microsecond values, while a datetime(6) will. You can test it in MySql 5.6:

CREATE TABLE your_table (
  d1 datetime,
  d2 datetime(6)
);

INSERT INTO your_table VALUES
('2011-11-11 11:11:11.111111', '2011-11-11 11:11:11.111111');

SELECT MICROSECOND(d1) as m1, MICROSECOND(d2) as m2
FROM your_table;

m1 | m2
-----------
0  | 111111

If you are not using MySql 5.6+ I would suggest you to use two columns, one for the datetime part, and one for the microseconds:

CREATE TABLE your_table (
  dt datetime,
  us int
);

INSERT INTO your_table VALUES
('2011-11-11 11:11:11.111111', MICROSECOND('2011-11-11 11:11:11.111111'));