Time & Date Stamp in a MySQL table row

Question

Hello,

I would like to put a time & date stamp on each row added to a MySQL table. If I understand correctly, I need to create a column for the time & date stamp. How do I do that for the CREATE TABLE query below?

"CREATE TABLE `$table` (id INT(11) NOT NULL auto_increment, site VARCHAR(1000) NOT NULL, actions1 BIGINT(9) NOT NULL, actions2 BIGINT(9) NOT NULL, PRIMARY KEY(id), UNIQUE (site))"

Also, if I understand correctly, I would then need to add the stamp each time a row is inserted to the table. How would I do that for the INSERT INTO query below?

"INSERT INTO `$find` VALUES (NULL, '$site',1,0)"

Thanks in advance,

John

Answer 1

You need to add a TIMESTAMP column like this:

CREATE TABLE `$table` (
 id INT(11) NOT NULL auto_increment, 
 site VARCHAR(1000) NOT NULL, 
 actions1 BIGINT(9) NOT NULL, 
 actions2 BIGINT(9) NOT NULL, 
 CreatedDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 PRIMARY KEY(id), UNIQUE (site))

That will make a column CreatedDateTime which contains the (server) time the row was created.

You can do the insert as:

INSERT INTO `$find` (site, actions1, actions2) VALUES ('$site', 1, 0)

For further reference see here