Trying to add a column of Mysql timestamp through hook_update

Question

I was trying to add the column of MYSQL Timestamp to the Database Table, the Query seems to be working correctly in mysql console.But when I execute the same in db_query() the following error is thrown.

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'last_update_date': ALTER TABLE stores ADD last_update_date TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';; Array ( ) in store_update_7100() (line 254 of /var/www/slocamo_app/sites/all/modules/store/store.install).

Is the Query.

ALTER TABLE  `stores` ADD  `last_update_date` TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00');

Answer 1

While valid in SQL, there is no data type TIMESTAMP in the Drupal 7 database schema.

The list if valid data types is here.

This means that you cannot add this datatype to your tables through any function that goes to the Drupal API (such as db_query). Your SQL console is not subject to these limitations, and will allow it to be added.

So the short answer is: Drupal 7 will not let you do what you try to do.

Also, in case you're trying to add two auto-update timestamps, note the following limitation in MySQL:

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.