Skip navigation links

MySQL Forums :: Performance :: Timestamp vs Datetime


Advanced Search

Re: Timestamp vs Datetime
Posted by: Allen Arakaki ()
Date: July 21, 2005 05:36PM

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 ...

Options: ReplyQuote


Subject Views Written By Posted
Timestamp vs Datetime 83689 Tom Dean 07/17/2005 05:33PM
Re: Timestamp vs Datetime 45792 Benoit St-Jean 07/17/2005 09:37PM
Re: Timestamp vs Datetime 30795 Erin ONeill 07/18/2005 04:51PM
Re: Timestamp vs Datetime 23201 Allen Arakaki 07/21/2005 05:36PM
Re: Timestamp vs Datetime 54808 Allen Arakaki 07/21/2005 05:59PM
Re: Timestamp vs Datetime 22981 Erin ONeill 07/22/2005 11:41AM
Re: Timestamp vs Datetime 15069 Erick Papadakis 11/21/2006 01:28PM
Re: Timestamp vs Datetime 14637 KimSeong Loh 11/21/2006 05:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.