Strange behavior when updating timestamp column

user_11046379 0
Viewed 417

There is MariaDB-10.3.18 table as:

create table switching_log
(
    item_name varchar(20) NOT NULL,
    switched_off timestamp,
    switched_on timestamp
);

select * from switching_log;
+-----------+---------------------+---------------------+
| item_name | switched_off        | switched_on         |
+-----------+---------------------+---------------------+
| 201197010 | 2019-10-01 08:36:08 | 2019-10-01 08:35:49 |
+-----------+---------------------+---------------------+

When i update 'switched_off' then 'switched_on' is not changed

UPDATE switching_log set switched_off = now()   where item_name = '201197010';

select * from switching_log;
+-----------+---------------------+---------------------+
| item_name | switched_off        | switched_on         |
+-----------+---------------------+---------------------+
| 201197010 | 2019-10-01 08:40:28 | 2019-10-01 08:35:49 |
+-----------+---------------------+---------------------+

But when i update 'switched_on' then 'switched_off' updated too.

UPDATE switching_log set switched_on = now()   where item_name = '201197010';

select * from switching_log;
+-----------+---------------------+---------------------+
| item_name | switched_off        | switched_on         |
+-----------+---------------------+---------------------+
| 201197010 | 2019-10-01 08:40:40 | 2019-10-01 08:40:40 |
+-----------+---------------------+---------------------+
mariadb timestamp mysql sql
1 Answers

This behavior is by design:

MariaDB has special behavior for the first column that uses the TIMESTAMP data type in a specific table. For the first column that uses the TIMESTAMP data type in a specific table, MariaDB automatically assigns the following properties to the column:

DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP

This means that if the column is not explicitly assigned a value in an INSERT or UPDATE query, then MariaDB will automatically initialize the column's value with the current date and time.

To solve the problem, disable "automatic values" feature of timestamp column(s). The simplest and predictable approach would be:

create table switching_log
(
    item_name varchar(20) NOT NULL,
    switched_off timestamp NULL,
    switched_on timestamp NULL
);

Demo on db<>fiddle

user_870150
Related