Problem with the DATETIME field in MySQL

Question

Problem with the DATETIME field in MySQL

Navigation

#1 by (3 votes)

1

In my MySQL database in phpMyAdmin I have a table in which when I enter a date in a field of the table I put the date in millisecond format.

Clicking on the field to select a calendar date appears as follows:

With the bar to select the milliseconds. And if I select any day, the field is saved in this way:

I have tried to change the configuration of the table and delete and re-create the field but the same thing still happens. It happens to me in any field of type DATETIME .

This is the configuration of this table in particular:

What can be happening?

mysql phpmyadmin

asked by M. Giner 16.05.2018 в 07:49

source

1 answer

Differentiate PHP sessions between systems The user is not registered in the database

score 3 · Accepted Answer

The problem you suffer is because you have defined the field using a parameter in parentheses (or perhaps through a table editor or GUI that added that value to you).

If you define a field with DATETIME(6) the date fields will have 6 decimal places:

CREATE TABLE test (
  fecha DATETIME(6)
);

To create the table correctly you must use DATETIME without any value in parentheses or a value (0) to indicate "0 decimal digits":

CREATE TABLE test (
  fecha DATETIME(0)
);

To change the definition without losing the data stored in the table you can use:

ALTER TABLE test
  MODIFY fecha DATETIME(0);

If what you want is only year, month and day then you should use a type DATE instead:

ALTER TABLE test
  MODIFY fecha DATE;

You can see an example online at this link .