CODEWITHSUNDEEP
mysql
piano7heart
piano7heart

Reputation: 439

How to insert a microsecond-precision datetime into mysql？

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?

Upvotes: 28

Views: 51309

Answers (4)

fthiella
fthiella

Reputation: 49049

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'));

Upvotes: 65

IgK
IgK

Reputation: 81

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.

Upvotes: 8

Radha Ramaiah
Radha Ramaiah

Reputation: 31

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'

Upvotes: 3

eggyal
eggyal

Reputation: 125865

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.

Upvotes: 3

Related Questions