Timestamp in MySQL not updating despite updating records

Question

I have a column which contains a timestamp in my MySQL database. No matter how many times I update my records, my timestamps are not updated. Is there anything I should do to make it auto update?

Searching other questions here in Stackoverflow, most of them say the opposite, it's really very confusing! For example, this one, Should I use field 'datetime' or 'timestamp'? (Look at the top rated answer)

Thank you

Here is my table structure

+----------------+-------------+------+-----+-------------------+----------------+
| Field          | Type        | Null | Key | Default           | Extra          |
+----------------+-------------+------+-----+-------------------+----------------+
| Num            | int(11)     | NO   | PRI | NULL              | auto_increment |
| mem_name       | varchar(50) | NO   |     | NULL              |                |
| mem_id         | int(11)     | NO   |     | NULL              |                |
| car_number     | varchar(11) | NO   |     | NULL              |                |
| firstcall_time | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| tow_time       | datetime    | NO   |     | NULL              |                |
+----------------+-------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)

One Record:

+-----+----------+--------+------------+---------------------+---------------------+
| Num | mem_name | mem_id | car_number | firstcall_time      | tow_time            |
+-----+----------+--------+------------+---------------------+---------------------+
|   1 | Jakey    |  54564 | SHA34345   | 2013-02-19 16:14:12 | 2013-02-19 16:32:45 |
+-----+----------+--------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

After Updating:

+-----+----------+--------+------------+---------------------+---------------------+
| Num | mem_name | mem_id | car_number | firstcall_time      | tow_time            |
+-----+----------+--------+------------+---------------------+---------------------+
|   1 | Tommy    |  54564 | SHA34345   | 2013-02-19 16:14:12 | 2013-02-19 16:32:45 |
+-----+----------+--------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

Does this field has `ON UPDATE...` clause defined? Can you show the results of `SHOW TABLE...` for the table you speak about? — raina77ow, Feb 19 '13 at 08:41
I created the table from phpmyadmin by choosing the fields in the drop down menu, I'm pretty new to SQL. Sorry, can you give me the full command? I tried "SHOW TABLE tbl_name", it gives an error. — Belgarion, Feb 19 '13 at 08:49
Do you have multiple timestamp columns. Can you post your table structure here. — iiro, Feb 19 '13 at 08:50
Guys, any ideas? I posted an updated entry of the table too! :) — Belgarion, Feb 19 '13 at 09:04

Meherzad · Accepted Answer · 2013-02-19T09:20:22.523

The main reason that your timestamp is not updating beacuse you have not added ON UPDATE CURRENT_TIMESTAMP clause in your table creation statement.

Change it with alter table command and modify your column

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, 
the column has the current timestamp for its default value and 
is automatically updated to the current timestamp.

You must add On UPDATE CURRENT_TIMESTAMP

CREATE TABLE t1 (
  id int, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ON UPDATE CURRENT_TIMESTAMP);

Edit - Table modifying command

alter table <tablename> modify <columnName> TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP;

Refer http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

I see, I created my table in phpmyadmin, is there a way to add the ON UPDATE CURRENT_TIMESTAMP through the Phpmyadmin interface without manually typing the ALTER table command? — Belgarion, Feb 19 '13 at 09:10

score 5 · Answer 2 · answered Oct 20 '13 at 13:16

5

Add On UPDATE CURRENT_TIMESTAMP

MySQL Query:

ALTER TABLE `table_name` CHANGE `date_field` `date_field` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Hope it helps !

answered Oct 20 '13 at 13:16

Aditya P Bhatt

21,431
18
85
104

Timestamp in MySQL not updating despite updating records

2 Answers2