28

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?

fthiella
  • 48,073
  • 15
  • 90
  • 106
piano7heart
  • 439
  • 1
  • 5
  • 11
  • How do you know that it becomes `2011-11-11 11:11:11`? Where did you see it formatted like so? Also what datatype is the column you are storing this value into? – Darin Dimitrov Dec 26 '12 at 09:51
  • check this question: http://stackoverflow.com/questions/2572209/why-doesnt-mysql-support-millisecond-microsecond-precision – Zeina Dec 26 '12 at 09:57

4 Answers4

62

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'));
fthiella
  • 48,073
  • 15
  • 90
  • 106
  • Thank you for your correctting my question and your answer very much。My English is so poor。 – piano7heart Dec 27 '12 at 12:19
  • 3
    Word to the wise: don't use ms to signify microseconds, as ms is the official sign for milliseconds. – ironMover Jun 20 '14 at 01:54
  • The above SQL statements work for me but I can't Create Table or Alter Table with fractional seconds columns using Workbench GUI. – Automate Jul 27 '14 at 13:28
  • Can you find a reference that this is `5.6+`? I don't see it in the 5.6 release notes. – Jon Surrell May 11 '17 at 08:52
  • The 5.5 fractional time is a good source I suppose: "when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it." https://dev.mysql.com/doc/refman/5.5/en/fractional-seconds.html – Jon Surrell May 11 '17 at 08:54
8

use data type of datetime(6) or timestamp(6) in your table and insert current time with NOW(6) to get 6 digits of fractional seconds.

IgK
  • 81
  • 1
  • 4
3

As documented under Fractional Seconds in Time Values:

A trailing fractional seconds part is permissible for temporal values in contexts such as literal values, and in the arguments to or return values from some temporal functions. Example:

mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473');
+-------------------------------------------+
| MICROSECOND('2010-12-10 14:12:09.019473') |
+-------------------------------------------+
|                                     19473 |
+-------------------------------------------+

However, when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it.

Note that this behaviour has changed in v5.6.4.

eggyal
  • 122,705
  • 18
  • 212
  • 237
3

Some samples of inserting datetime values into MySQL(5.6.17)

create database if not exists mydb;
use mydb;
drop table if exists sample;
create table sample (c1 integer, c2 datetime(6),c3 varchar(30));
insert into sample values (1, '2014-07-25 11:18:10.999999', 'Actual Given Value');
insert into sample values (2, now(6), 'From NOW(6) function');
insert into sample values (3, now(), 'From NOW() function');
insert into sample values (4, sysdate(6), 'From sysdate(6) function');
insert into sample values (5, sysdate(), 'From sysdate() function');

select * from sample;

 # c1, c2, c3
'1', '2014-07-25 11:18:10.999999', 'Actual Given Value'
'2', '2014-07-25 11:27:27.314114', 'From NOW(6) function'
'3', '2014-07-25 11:27:27.000000', 'From NOW() function'
'4', '2014-07-25 11:27:27.429121', 'From sysdate(6) function'
'5', '2014-07-25 11:27:27.000000', 'From sysdate() function'
Radha Ramaiah
  • 31
  • 1