current_timestamp in date field

Question

How can I add current_timestamp or now for date feild in drupal database schema.

        'created' => array(
            'description' => t('Timestamp when the fee schedule was added.'),
            'type' => 'int',
            'not null' => TRUE,
            'unsigned' => TRUE,
        ),

Answer 1

You can specify a driver-specific column type for MySQL using the mysql_type attribute:

function MYMODULE_schema (){
  $schema['table_name'] = array(
    'fields' => array(
      'created' => array(
        'mysql_type' => 'timestamp',
        'not null' => TRUE
      )
    )
  );

  return $schema;
}

Then, on enabling your module, you can simply modify the column to add the default/on-update behaviour:

function MYMODULE_enable() {
  db_query('
    ALTER TABLE {table_name} 
    MODIFY created TIMESTAMP NOT NULL 
    DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP'
  );
}

I've just tested that on Drupal 7 and it works a treat.

NOTE: If you don't want the timestamp to update every time the record is updated then just remove the ON UPDATE CURRENT_TIMESTAMP part of the query.

Answer 2

I assume you are referring to MySQL, the feature you want to look into straight from the docs is time stamps.

This would probably update the db table created by schema to the field type you are looking for:

MODIFY COLUMN `created` TIMESTAMP NOT NULL DEFAULT current_timestamp;

Just stumbled on this post stating timestamp support should be in for Drupal 8, so I assume (and I coud be wrong) it probably is not supported out of the box for D7. Just change the type to TIMESTAMP in your array, and see what gives but I would not wager on this working through the D7 schema api.

UPDATE:

Actually, look in the post. The patch will add support to timestamp in D7. Major kudos to drupalshrek for sharing this with us.

Database schema, once patched would be as follow:

<?php
$schema['vchess_moves'] = array(
  'description' => 'Contains the list of moves for each game',
  'fields' => array(
    'timestamp' => array(
    'description' => 'Exact date and time of the move',
    'type' => 'timestamp',
    'not null' => TRUE,
?>

Go see the post above for the patch, I won't repost his stuff here, not sure if doing so would be proper.

Hope this helps, happy coding.

Answer 3

Here is the solution for Drupal 8.6.x just incase if someone is looking as I was :)

'created' => [
  'mysql_type' => 'timestamp',
  'not null' => TRUE,
  'mysql_default' => CURRENT_TIMESTAMP,
 ],

this is equivalent to ...

`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP