This is the MySQL table that I want, but focus on datum_en_tijd
:
CREATE TABLE `navigatie` (
`navigatie_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`navigatie_id`),
`huidige_vraaggroep` varchar(255) NOT NULL,
`vorige_vraaggroep` varchar(255) DEFAULT NULL,
`richting` varchar(255) NOT NULL,
`datum_en_tijd` timestamp(3) NOT NULL,
`schadegeval_id` bigint(20) UNSIGNED DEFAULT NULL,
`claim_id` bigint(20) UNSIGNED DEFAULT NULL,
`gebruiker_id` bigint(20) NOT NULL,
`soort_gebruiker` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
As you can see TIMESTAMP
is with (3)
for milliseconds
Whenever I try to Alter Table... in MySQL Workbench I get this error:
When I do View DDL, I get a new tab with this query:
delimiter $$
CREATE TABLE `navigatie` (
`navigatie_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`huidige_vraaggroep` varchar(255) NOT NULL,
`vorige_vraaggroep` varchar(255) DEFAULT NULL,
`richting` varchar(255) NOT NULL,
`datum_en_tijd` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
`schadegeval_id` bigint(20) unsigned DEFAULT NULL,
`claim_id` bigint(20) unsigned DEFAULT NULL,
`gebruiker_id` bigint(20) NOT NULL,
`soort_gebruiker` varchar(255) NOT NULL,
PRIMARY KEY (`navigatie_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
Note the change on
`datum_en_tijd` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
Is this a bug or what? Also note the SYNTAX ERROR on line 8 MySQL WorkBench gives us: I'm running MySQL 5.6.16
Incompatible change: In very old versions of MySQL (prior to 4.1), the TIMESTAMP data type supported a display width, which was silently ignored beginning with MySQL 4.1. This is deprecated in MySQL 5.1, and removed altogether in MySQL 5.5. These changes in behavior can lead to two problem scenarios when trying to use TIMESTAMP(N) columns with a MySQL 5.5 or later server:
When importing a dump file (for example, one created using mysqldump) created in a MySQL 5.0 or earlier server into a server from
a newer release series, a CREATE TABLE or ALTER TABLE statement containing TIMESTAMP(N) causes the import to fail with a syntax error.
To fix this problem, edit the dump file in a text editor to replace any instances of TIMESTAMP(N) with TIMESTAMP prior to
importing the file. Be sure to use a plain text editor for this, and not a word processor; otherwise, the result is almost certain to be unusable for importing into the MySQL server.
http://dev.mysql.com/doc/refman/5.1/en/timestamp-initialization.html
So you cant have
`datum_en_tijd` timestamp(3)
instead you need to use
`datum_en_tijd` timestamp
or
`datum_en_tijd` datetime(3)