How to implement a mysql date or datetime field correctly in hook_schema()?

Question

I have written a mymodule.install file which contains a schema definition to create a table in my database. This table contains two data fields. Theese fields will be populated by the users when they will fill in the specific content (example: to specific a publish date for a news). Now i didn't used the Date contributed module on purpose, cause i needed that theese two fields were on the same row in the database table for other reasons.

The hook_schema defines the two fields this way:

'pubblish_date' => array(
    'description' => t('The pubblish date for the single news'),
    'mysql_type' => 'datetime',
    'not null' => FALSE,
  ),
  'unpublish_date' => array(
    'description' => t('The unpublish date for the single news'),
    'mysql_type' => 'datetime',
    'not null' => FALSE,
  ),

The table is created correctly in the database, but i always get theese advice messages:

Field news_board.pubblish_date: no Schema type for mysql type datetime. Field news_board.unpublish_date: no Schema type for mysql type datetime. news_board.pubblish_date: no type for Schema type :normal. Field news_board.pubblish_date: no Schema type for type . news_board.unpublish_date: no type for Schema type :normal. Field news_board.unpublish_date: no Schema type for type .

Seems strange to me cause I red on the documentation to use the mysql_type specification in order to store the datetime format in mysql database.

I know that Drupal supports natively timestamps and if you want to store a different date format you have to use specific definitions like mysql_type or pgsql_type according on the database yuo are using.

In discussions I found online lot of people used mysql_type definition and from what I seen they solved the problem, so why it doesn't work for me?

Thanks a lot.

Answer 1

You should provide a fallback type (varchar) for the case that the database is not mysql.

'pubblish_date' => array(
  'description' => t('The pubblish date for the single news'),
  'type' => 'varchar',
  'mysql_type' => 'datetime',
  'not null' => FALSE,
),
'unpublish_date' => array(
  'description' => t('The unpublish date for the single news'),
  'type' => 'varchar',
  'mysql_type' => 'datetime',
  'not null' => FALSE,
),

However, the messages you see come from the Schema module. This problem is already recorded as issue #468644.

The implementation of the mysql datetime in your schema is fine. You can safely ignore these messages. I would say that these "advice messages" are caused by a bug in the Schema module.

Answer 2

If you need to use a record type not included in the officially supported list of types , you can specify a type for each database backend.

In this case, you can leave out the type parameter, but be advised that your schema will fail to load on backends that do not have a type specified. A possible solution can be to use the "text" type as a fallback. (https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21database.api.php/group/schemaapi/8.2.x)

'submit_date' => array( 'type' => 'varchar', 'mysql_type' => 'datet', )