MySQL Forums :: Performance :: Timestamp vs Datetime

I ran the following on 4.1.x

DROP TABLE IF EXISTS table1;
CREATE TABLE table1(
itemId BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
value BIGINT NOT NULL,
created DATETIME NOT NULL,
lastUpdated TIMESTAMP
)ENGINE = InnoDB;

DROP TABLE IF EXISTS table2;
CREATE TABLE table2(
itemId BIGINT NOT NULL PRIMARY KEY,
value BIGINT NOT NULL,
created DATETIME NOT NULL,
lastUpdated TIMESTAMP,
FOREIGN KEY (itemId) REFERENCES table1(itemId) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE = InnoDB;

INSERT INTO table1 VALUES(NULL, 1, NOW(), NULL);
INSERT INTO table2 VALUES(1, 1, NOW(), NULL);
SELECT t1.*, t2.* FROM table1 AS t1 LEFT JOIN table2 AS t2 ON (t1.itemId=t2.itemId);
+--------+-------+---------------------+---------------------+--------+-------+---------------------+---------------------+
| itemId | value | created | lastUpdated | itemId | value | created | lastUpdated |
+--------+-------+---------------------+---------------------+--------+-------+---------------------+---------------------+
| 1 | 1 | 2005-07-21 17:29:47 | 2005-07-21 17:29:47 | 1 | 1 | 2005-07-21 17:29:47 | 2005-07-21 17:29:47 |
+--------+-------+---------------------+---------------------+--------+-------+---------------------+---------------------+
1 row in set (0.00 sec)
UPDATE table1 SET itemId=2 WHERE itemId=1;
SELECT t1.*, t2.* FROM table1 AS t1 LEFT JOIN table2 AS t2 ON (t1.itemId=t2.itemId);
+--------+-------+---------------------+---------------------+--------+-------+---------------------+---------------------+
| itemId | value | created | lastUpdated | itemId | value | created | lastUpdated |
+--------+-------+---------------------+---------------------+--------+-------+---------------------+---------------------+
| 2 | 1 | 2005-07-21 17:29:47 | 2005-07-21 17:30:39 | 2 | 1 | 2005-07-21 17:29:47 | 2005-07-21 17:29:47 |
+--------+-------+---------------------+---------------------+--------+-------+---------------------+---------------------+
1 row in set (0.01 sec)

The results show that you can use NOW() with DATETIME. In addition, I would have expected the FOREIGN KEY CONTRAINT to update table2.lastUpdated (TIMESTAMP) - but it didn't. I believe multi-table updates will update the TIMESTAMP for every table.

Anyway, I included the statements so you can run it on your version ...