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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

Method 2

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.

Method 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.

Method 4

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'

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating