langjacques langjacques - 2 months ago 9x
MySQL Question

mysql datetime default 0001-01-01 00:00:00 on update 0001-01-01 00:00:00

I have a table, let's call it employees, that sends data to a web server, whenever this happens a datetime field is updated with a current_timestamp.

Now, when a record changes, I want to reset the datetime field to 0001-01-01 00:00:00 so that it will send the new information again.

Is it possible to do something like

DEFAULT 0001-01-01 00:00:00 ON UPDATE 0001-01-01 00:00:00

instead of



on update only supports current_timestamp, no other value is allowed, see Automatic Initialization and Updating for TIMESTAMP and DATETIME:

Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP and DATETIME. The DEFAULT clause also can be used to specify a constant (nonautomatic) default value; for example, DEFAULT 0 or DEFAULT '2000-01-01 00:00:00'.

You can either use an after update trigger, but the common solution is to use another column and set the value to current_timestamp on export and compare that to the latest update:

create table tablename (
   updated_at datetime(6) default current_timestamp(6) 
                          on update current_timestamp(6),
   exported_at datetime(6) default '0001-01-01 00:00:00'

To get all rows that needs to be exported, use

select * from tablename where exported_at <> updated_at for update;

To mark something as updated after an export, you then use

update tablename set exported_at = current_timestamp(6);

(Everything inside a transaction of course)