MariaDB: Incorrect datetime value というエラーへの対策

次のエラーが出た時の対策です。

エラーの状況

create table cities (id varchar(10) primary key, name text, population int, date_mod timestamp);
insert into cities set id='t3325',name='笠岡',population=741256,date_mod='1969-9-17';

実行結果

MariaDB [city]> drop table if exists cities;
Query OK, 0 rows affected (0.187 sec)

MariaDB [city]> create table cities (id varchar(10) primary key, name text, population int, date_mod timestamp);
Query OK, 0 rows affected (0.268 sec)

MariaDB [city]> insert into cities set id='t3325',name='笠岡',population=741256,date_mod='1969-9-17';
ERROR 1292 (22007): Incorrect datetime value: '1969-9-17' for column `city`.`cities`.`date_mod` at row 1
MariaDB [city]>

解決方法

timestamp を datetime に変更します。

create table cities (id varchar(10) primary key, name text, population int, date_mod datetime);
insert into cities set id='t3325',name='笠岡',population=741256,date_mod='1969-9-17';

実行結果

MariaDB [city]> drop table if exists cities;
Query OK, 0 rows affected (0.142 sec)

MariaDB [city]> create table cities (id varchar(10) primary key, name text, population int, date_mod datetime);
Query OK, 0 rows affected (0.154 sec)

MariaDB [city]> insert into cities set id='t3325',name='笠岡',population=741256,date_mod='1969-9-17';
Query OK, 1 row affected (0.018 sec)

MariaDB [city]>