Framework: timestamp fields on migration alway defaults to CURRENT_TIMESTAMP

[aprilmintacpineda]

• Laravel Version: 5.5.19
• PHP Version: 7.0.22-0ubuntu0.16.04.1
• Database Driver & Version: mysql Ver 14.14 Distrib 5.7.19, for Linux (x86_64)

Why is all timestamp on migrations default to current_timestamp?

On my migrations, I have something like this:

$table->timestamp('expiry_date');

but when I do

mysql > describe `table`;

I get

+----------------------------+------------------+------+-----+-------------------+-----------------------------+
| Field                      | Type             | Null | Key | Default           | Extra                       |
+----------------------------+------------------+------+-----+-------------------+-----------------------------+
| confirm_email_token_expiry | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

I was expecting

+----------------------------+------------------+------+-----+-------------------+-----------------------------+
| Field                      | Type             | Null | Key | Default           | Extra                       |
+----------------------------+------------------+------+-----+-------------------+-----------------------------+
| confirm_email_token_expiry | timestamp        | NO   |     | NULL              |                             |

EDIT[1]

manually setting the default value to null still gives me the same output.

$table->timestamp('expiry_date')->default(null);

[sisve]

Sounds like a mysql configuration issue.

If explicit_defaults_for_timestamp is disabled, the server enables the nonstandard behaviors and handles TIMESTAMP columns as follows:

1. TIMESTAMP columns not explicitly declared with the NULL attribute are automatically declared with the NOT NULL attribute. Assigning such a column a value of NULL is permitted and sets the column to the current timestamp.
2. The first TIMESTAMP column in a table, if not explicitly declared with the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is automatically declared with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

Source: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

[warren32]

Yes, this is mysql configuration. To acheive your desired results, try calling the nullable() method:

$table->timestamp('expiry_date')->nullable()->default(null);

[alexweissman]

It's worth pointing out that MariaDB does this as well - for some ungodly reason it automatically added the ON UPDATE CURRENT_TIMESTAMP attribute to one of my custom timestamps as well.

Although this is an issue in the database layer, not the application layer, it's still enough of a gotcha that it might be worth making a note of in the migrations documentation.