MySQL Timestamp - why all zeros?

Question

I'm using PHPMyAdmin and I've got a MySQL table column called "timestamp." The type (surprise!) is TIMESTAMP, and in 'attributes' I've set it to ON UPDATE CURRENT_TIMESTAMP.

However, each new record gets a timestamp that looks like this:

0000-00-00 00:00:00

I have explicitly set the default value to none, but when I save and come back to look, it is set to all zeros as above.

The relevant PHP records page hits with this query:

$query = "INSERT INTO `pagehit` (user_id, pageurl)
VALUES ('" . $userid . "', '" . $pageurl . "')";

The whole thing is running under XAMPP.

What am I missing?

Answer 1

Try setting the default value to CURRENT_TIMESTAMP instead of putting that in the attributes.

MySQL Reference

Answer 2

What am I missing?

You don't update :)

Use DEFAULT CURRENT_TIMESTAMP along with ON UPDATE CURRENT_TIMESTAMP

Answer 3

If your "timestamp " column captures only the insertion time then use only

timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP

otherwise if it is for modification time then use like as follows

timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Answer 4

wat if i have 2 columns: created n updated.. and when i add new data.. only the updated column has the updated date n time where as in the created column its 0000-00-00 00:00:00.. how do i get my created column to have its initial date n time.?