2

column date is timestamp - default value - CURRENT_TIMESTAMP

I choose this because want a current datetime on inserting a new row.

But is is changed each time when another column is updated.

Is there a way to keep current_timestamp only on inserting a new row and not on updating another columns?

GMB
  • 216,147
  • 25
  • 84
  • 135
qadenza
  • 9,025
  • 18
  • 73
  • 126

3 Answers3

4

A TIMESTAMP column updates automatically on every update. A DATETIME column doesn't.

You want:

your_column DATETIME DEFAULT CURRENT_TIMESTAMP

This lets you specify a default value at the time of insertion, like you would for any column, but because it's not a TIMESTAMP anymore it will not continue to update itself with each future UPDATE.

VoteyDisciple
  • 37,319
  • 5
  • 97
  • 97
  • `This lets you specify a default value at the time of insertion like you would for any column` - pls explain - if I specify something at the time of insertion - it's not a default. – qadenza Jan 08 '19 at 15:44
  • The `DEFAULT` keyword is what specifies the default value at the time of insertion. If you omit the column in your `INSERT`, then `CURRENT_TIMESTAMP` is what will go in that column. – VoteyDisciple Jan 08 '19 at 19:09
1

From the documentation :

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

So you to avoid the my_date_col column to auto-update on UPDATE, you want to explicitly preserve its value, like :

UPDATE my_table SET my_col1 = 'foo', my_col2 = 'bar', my_date_col = my_date_col
WHERE ...
GMB
  • 216,147
  • 25
  • 84
  • 135
0

When you run

SHOW FULL COLUMNS FROM `moufa`; -- where `moufa` is the name of the table in the example

you will be able to see something like this

+-------+-------------+-----------+------+-----+---------------------+-------------------------------+---------------------------------+---------+
| Field | Type        | Collation | Null | Key | Default             | Extra                         | Privileges                      | Comment |
+-------+-------------+-----------+------+-----+---------------------+-------------------------------+---------------------------------+---------+
| id    | smallint(6) | NULL      | NO   | PRI | NULL                | auto_increment                | select,insert,update,references |         |
| ts    | timestamp   | NULL      | NO   |     | current_timestamp() | on update current_timestamp() | select,insert,update,references |         |
+-------+-------------+-----------+------+-----+---------------------+-------------------------------+---------------------------------+---------+

The problem is on the ts field the Extra. To check your the table run

SHOW CREATE TABLE `moufa`;

The most common case like GMB posted the column has been auto-updated.

Now to avoid further cases like this, you should explicitly define the the default value for the column.

DROP TABLE IF EXISTS `moufa`; -- just for the example
CREATE TABLE `moufa`( 
    `id` SMALLINT NOT NULL AUTO_INCREMENT, 
    `ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(`id`)
);

Or rather than dropping and recreating a table

ALTER TABLE `moufa`
  MODIFY COLUMN `ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
gmastro
  • 126
  • 3