MySql TIMESTAMP column is auto updated. Why?

Question

I have a table created with this SQL:

CREATE TABLE `test_table` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `data` TEXT,
  `timestamp` TIMESTAMP
) CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE InnoDB;

But when I run SHOW CREATE TABLE test_table, I get:

CREATE TABLE `test_table` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `data` text COLLATE utf8_unicode_ci,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Why the timestamp column being assigned ON UPDATE CURRENT_TIMESTAMP by default? I do not know how to get rid of this. Any help would be greatly appreciated.

Go to php my admin, change 'default' option to As defined and select first option in attributes section of 'timestamp' column of 'test_table' — Nana Partykar, Dec 18 '15 at 12:46
using **phpmyadmin** and altering this everytime is a heck, I have 25-30 tables created like this. And may create more in future. Is there any SQL way to stop this? — VPZ, Dec 18 '15 at 12:53

score 4 · Answer 1 · answered Dec 18 '15 at 12:56

4

If you want that to your timestamp will be not added automatically ON UPDATE you have to define it like this

CREATE TABLE `test_table` (
   ...
   `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ...

answered Dec 18 '15 at 12:56

Armen

4,064
2
23
40

can I omit the **DEFAULT CURRENT_TIMESTAMP** part? – VPZ Dec 18 '15 at 13:00
nevermind. just checked and nope, I can't! However, I used **DEFAULT 0** to prevent this. – VPZ Dec 18 '15 at 13:03
you can use instead `DEFAULT NULL`, or use `datetime` column type LIKE: timestamp datetime DEFAULT NULL as @thaspius suggested – Armen Dec 18 '15 at 13:06
Declare the column as `TIMESTAMP NULL DEFAULT NULL` if you want it nullable and no auto behavior. – Michael - sqlbot Dec 18 '15 at 21:50

score 1 · Accepted Answer · edited May 23 '17 at 12:04

1

Use a datetime field instead

Should I use field 'datetime' or 'timestamp'?

Timestamps in MySQL generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

edited May 23 '17 at 12:04

Community

1
1

answered Dec 18 '15 at 12:52

thaspius

1,135
3
17
33

can I safely alter my table.columns to *datetime* from *timestamp*? please note, my tables have data. – VPZ Dec 18 '15 at 12:56
2

Yes, you can. In general though, I would *highly* recommend testing any table changes with production data in them on a test database. – thaspius Dec 18 '15 at 12:58

MySql TIMESTAMP column is auto updated. Why?

2 Answers2