How to define a timestamp field using drupal6 schema

Question

In this way I can create a table with a timestamp field in MySQL:

CREATE TABLE foo (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  created_at TIMESTAMP,
  name varchar(255)
);

Which would automatically insert a timestamp value each time a new row is inserted.

For example:

INSERT INTO foo (name) VALUES ('bar');
SELECT * FROM foo;

Output:

+----+---------------------+------+
| id | created_at          | name |
+----+---------------------+------+
|  1 | 2012-03-12 10:47:02 | bar  |
+----+---------------------+------+

How can I create a timestamp field like this using the drupal6 schema API?

Answer 1

In short, you cannot do this because the schema API does not support the TIMESTAMP type.

The MySQL implementation of the Schema API implements the datetime field using MySQL's DATETIME type. That type does not support automatic initializiation/update to the current time.

MySQL's type TIMESTAMP supports automatic initializiation/update to the current time. Switching from DATETIME to TIMESTAMP is not an option, though:

TIMESTAMP behaves differently in MySQL versions 4.1, 4.1.2, 4.1.3, 4.1.6 The first TIMESTAMP column in a table behaves different than other TIMESTAMP columns in that table.