Adding Timestamp to Database Table

Question

I have an html/php form that updates entries on the database server. I need to add a field to each row indicating when that entry is added, so in other words a timestamp of when the entry was created. I have been searching and found this:

http://www.createafreewebsite.net/phpmysql/alter.html

Would I do something like:

$timestamp = time();
mysql_query("ALTER TABLE notification
ADD timestamp CHAR(30) AFTER names);

mysql_query("INSERT INTO notification (`timestamp`) values ('$timestamp');

is this the correct way to approach it, and am I using the correct datatype? I would need to compare the timestamp with another timestamp generated from a javascript file later on. For example, if timestamp1 is smaller than timestamp2 than perform following functions...

Any information would be helpful, thanks!

EDIT:

Provided information as requested:

So far I have:

mysql_query("INSERT INTO notification (`program`, `month`, `day`, `year`, `sponsor`, `type`, `category`, `range`, `desc`) values ('$pName' ,  '$month' , '$day' , '$year' , '$sponsor' , '$type' , '$category' , '$range' , '$desc')");

Would you mind posting the structure of the table to which you're trying to add the column? Using that INSERT statement won't insert any data into the other columns of the table, and will fail if one or more of them is NOT NULL and without a default. — David Faber
– David Faber, Commented Mar 20, 2012 at 14:01

472084 · Accepted Answer · 2012-03-20 13:37:36Z

4

time() in PHP will produce a timestamp, your MySQL table might be expecting another format, so you can just do:

mysql_query("INSERT INTO notification (`timestamp`) values (NOW());

and it will work with date and datetime fields too.

Even though your table is CHAR(30) you still have one less variable to use.

Of if you change your column data type to TIMESTAMP then you can use on update CURRENT_TIMESTAMP to fill the table cell for you.

edited Mar 20, 2012 at 13:37

answered Mar 20, 2012 at 13:32

472084

17.9k10 gold badges67 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1152440 Over a year ago

Hey thanks for the reply, will this generate a numerical timestamp, or something formatted that the user can read? I'm actually thinking doing mathematical comparison would be easier with just a numerical timestamp, like 1332249169 instead of Tue Mar 20 2012 09:02:49.

472084 Over a year ago

its best to use MySQLs date formats, you can easily convert them to timestamps using strtotime()in PHP

user1152440 Over a year ago

Nice!, Can this strtotime() also be done with javascript? I'm thinking of reading the table timestamp column from the database with js and comparing it to the current time.

472084 Over a year ago

Well JS cant read the table without PHPs help, so you can just use strtotime() before you pass the data to the JS.

David Faber Over a year ago

"you can use on update CURRENT_TIMESTAMP to fill the table cell for you." - Wouldn't that also update the column on an UPDATE as well as an INSERT? Of course, that may be what the OP wants.

Stewie · Accepted Answer · 2012-03-20 13:31:44Z

1

timestamp should have a timestamp datatype. http://dev.mysql.com/doc/refman/5.0/en/datetime.html

answered Mar 20, 2012 at 13:31

Stewie

3,1492 gold badges26 silver badges22 bronze badges

Comments

David Faber · Accepted Answer · 2012-03-20 13:42:49Z

1

You definitely do not want to use a column with CHAR or VARCHAR datatype to store a date or timestamp - it can make comparisons difficult later on. Also, you should consider putting a default on the timestamp column so that it is automatically populated when you insert a row, or using an insert trigger on the notification table to do the population. That way the chance of developer error is reduced.

CREATE TRIGGER notification_timestamp BEFORE INSERT
    ON notification FOR EACH ROW
BEGIN
    SET new.timestamp = NOW();
END;

Apologies if the syntax isn't quite right.

answered Mar 20, 2012 at 13:42

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

3 Comments

user1152440 Over a year ago

Hey thanks for the post, I'm trying to do what you suggested but I'm very new to SQL altogether. How would I 'use' your code, so far I've just been adding and editing tables with PHP code. How would the code know which column to insert the timestamp into?

David Faber Over a year ago

In a trigger the new table refers to the inserted/updated data, while the old table refers to the data that is being updated or deleted. So by using SET new.timestamp = NOW() you're telling MySQL to set the value of the timestamp column (by the way, you may want to pick another name for the column as timestamp is a datatype in MySQL) to the current time (NOW() is a synonym for CURRENT_TIMESTAMP()) and use it for the row to be inserted.

user1152440 Over a year ago

so CREATE TRIGGER name_of_timestamp_col BEFORE INSERT ON table_name FOR EACH ROW BEGIN SET new.timestamp = NOW(); END;

Collectives™ on Stack Overflow

Adding Timestamp to Database Table

3 Answers 3

5 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related