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.881in mysql. Actually, I can insert and get time 2009-09-22 18:09:37in mysql, but whenever I try to insert 2009-09-22 18:09:37.881, the data is not inserted into the database.

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

I created a table using the following query

Create table XYZ(MyTime DateTime);

I tried the following query, which worked fine

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

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

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

Answer 1

I solved the problem. The database did not allow time entry in milliseconds. Take a look at the lines below:

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

Answer 2

​​ MySQL 5.6.4. : http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

Answer 3

MySQL , , , , datetime,

" . ".

, Timestamp.

? , datetime ?

Answer 4

MySQL

[...] microseconds cannot be stored in a column of any temporary data type. Any portion of microseconds is discarded.

You will need some workaround.

Answer 5

<?
# PHP
$microtime = microtime(true);
$datetime = date("Y-m-d H:i:s");
?>

MYSQL

• CREATE field col_datetime - DATETIME)
• CREATE field col_microtime - DECIMAL (15,4)

ADD: INSERT to the values ​​of your_table (col_datetime, col_microtime) ('$ datetime', '$ microtime')

List new-> old: SELECT * FROM yout_table ORDER BY col_microtime DESC

List old-> new: SELECT * FROM yout_table ORDER BY col_microtime