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
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
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)