CODEWITHSUNDEEP
mysql
kevin
kevin

Reputation: 115

MYSQL CURRENT_TIMESTAMP only applied on update

I have a database with a column named date (timestamp) which is set to default CURRENT_TIMESTAMP. This used to work fine, however after migrating the database to a different server the timestamp only gets applied after an UPDATE query is performed, an INSERT query just sets it to "0000-00-00 00:00:00".

Upvotes: 0

Views: 57

Answers (1)

Skrrp
Skrrp

Reputation: 695

I suspect that whatever is doing the insert is putting an empty string into the field. Look at this result;

MariaDB [horizon]> create table foo (t timestamp default current_timestamp not null);
Query OK, 0 rows affected (0.02 sec)

MariaDB [horizon]> insert into foo values ('');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [horizon]> select * from foo;
+---------------------+
| t                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

I would check the code that is doing the insert - if it's coming from a web form the form itself could be submitting an empty string. You really need to insert null or leave the column out of the INSERT to get the default.

MariaDB [horizon]> insert into foo values (null);
Query OK, 1 row affected (0.01 sec)

MariaDB [horizon]> insert into foo values ();
Query OK, 1 row affected (0.00 sec)

MariaDB [horizon]> select * from foo;
+---------------------+
| t                   |
+---------------------+
| 0000-00-00 00:00:00 |
| 2017-09-19 21:41:16 |
| 2017-09-19 21:41:20 |
+---------------------+
3 rows in set (0.00 sec)

Upvotes: 1

Related Questions