How to insert time 2009-09-22 18:09:37.881 in MYSQL My column type is DateTime

Question

How to insert time (2009-09-22 18:09:37.881) in mysql. Actually I can insert and retrieve the time 2009-09-22 18:09:37 in mysql but whenever I am trying to insert 2009-09-22 18:09:37.881 data did not get inserted in database.

2009-09-22 18:09:37.881 -------> YYYY-MM-DD HH:MI:Sec.Ms

I have created a table using the below query

Create table XYZ(MyTime DateTime);

I tried the below query which worked fine insert into XYZ(MyTime) values('2009-09-22 18:09:37');

But I tried with the below query which did not work fine (Data didnot get insert in database)

insert into XYZ(MyTime) values('2009-09-22 18:11:38.881');

Thanks Sunil Kumar Sahoo

Answer 1

All time types in MySQL have only a precision of seconds

[...] microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded.

You'll need some workaround.

Answer 2

Hey there,

Having had a quick scan on the MySQL reference pages here, they seem to suggest that you cannot add milliseconds to a column of type datetime. The direct quote is

"However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded."

This implies that you cannot use Timestamp either.

Are the miliseconds necessary? Could they be stored in a secondary column and then recombined with the basic datetime upon retrieval?

Answer 3

Hi I got the issue resolved. Database will not allow to store time in millisecond. please have a look on the below line

CREATE TABLE MyTimeStamp(TimeData decimal(17,3));

insert into MyTimeStamp(TimeData) values (20090922201843.426);

SELECT timestamp(TimeData) FROM MyTimeStamp;

OutPut:

2009-09-22 20:018:43.426000

Thanks Sunil Kumar Sahoo