How to store unix timestamp as int with default and on update?-CodePudding

I have been updating my MySQL tables with the following:

ALTER TABLE logs ADD COLUMN updateTimeStamp timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp;

This stores the timestamp in the format of

2021-12-29 15:21:34

I tried originally to do the alter like so:

ALTER TABLE logs ADD COLUMN updateTimeStamp timestamp DEFAULT unix_timestamp() ON UPDATE unix_timestamp();

so that could store like 12121232, however that results in an error.

Is there anyway I can achieve the default and on update and store the timestamp in the format of 1212112, instead of the human readable datetime?

I know I can do SELECT unix_timestamt(columnname), but ideally I don't want to do that.

CodePudding user response：

If you want to automatically get an integer when you select the column, you need to make it an int (or int unsigned) type. You can set its default with default (unix_timestamp()) (the extra parentheses are needed when not using one of the historically allowed default values). And you will need to add a trigger to set it on update.

But I suggest you not do that; just use a timestamp type. You just make future trouble for yourself by not using the type designed to store timestamps.