Datetime type in Drupal 7 schema

Question

I'm writing a new Drupal 7 module (Drupal 7.10, Date 7.x-2.0-rc1 installed, Schema 7.x-1.0-beta3 installed) i defined a table in mymodule.install:

$schema['museums_tickets']= array(
    'fields' => array(
        'nid' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
    ),
    'day' => array(
        'type' => 'datetime', 
        'mysql_type' => 'DATETIME',
        'not null' => TRUE,
    ),
    'tickets' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        ),
    'ticket_code' => array(
            'type' => 'varchar',
            'length' => 32,
            'not null' => TRUE, 
            'default' => '',
        ),
    ),
    'primary key' => array('nid', 'day','ticket_code'),
);

but i obtain the following errors:

Field museums_tickets.day: no Schema type for mysql type datetime.
museums_tickets.day: no type for Schema type datetime.
Field museums_tickets.day: no Schema type for type datetime. 

The same applies if i use

'type' => 'datetime:normal',

I would like to know how to solve this problem. I don't want to store dates as timestamp, since another person wrote a lot of code and obviously i don't want to rewrite all. I already looked at drupal 7 custom schema error datetime but the answer doesn't work. Maybe i'm doing something wrong, but i don't find what.

Thank you in advance.

Answer 1

The Date contrib module has:

function date_field_schema($field) {
  $db_columns = array();
  switch ($field['type']) {
    case 'datestamp':
      $db_columns['value'] = array(
        'type' => 'int',
        'not null' => FALSE,
        'sortable' => TRUE,
        'views' => TRUE,
      );
      break;

    case 'datetime':
      $db_columns['value'] = array(
        'type' => 'datetime',
        'mysql_type' => 'datetime',
        'pgsql_type' => 'timestamp without time zone',
        'sqlite_type' => 'varchar',
        'sqlsrv_type' => 'smalldatetime',
        'not null' => FALSE,
        'sortable' => TRUE,
        'views' => TRUE,
      );
      break;

    default:
      $db_columns['value'] = array(
        'type' => 'varchar',
        'length' => 20,
        'not null' => FALSE,
        'sortable' => TRUE,
        'views' => TRUE,
      );
      break;
  }

Answer 2

The following works for me (Drupal 7.10):

'day' => array(
  'mysql_type' => 'DATETIME',
  'not null' => TRUE,
)

(notice the mysql_type key instead of type)

From the Schema docs:

If you need to use a record type not included in the officially supported list of types above, you can specify a type for each database backend. In this case, you can leave out the type parameter

datetime is not an allowed generic type in Drupal 7 so leaving the type key in there will always cause an error.

Answer 3

If you look in the schema of the Date module you will find something like

'day' => array(
    'type' => 'datetime',
    'mysql_type' => 'datetime',
)

Actually the answer of Clive was the one i picked first but later gave me problems with the Views module.

This implementation of the Date module save my day