Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
                According to the documentation, MySQL stores a TIMESTAMP: A four-byte integer representing seconds UTC since the epoch ('1970-01-01 00:00:00' UTC). When you query it, it will give you a timestamp that is humanly readable. If you want to store an integer without MySQL knowing that it's a timestamp, then you could do that, but you'll lose any functionality that it provided with the timestamps.
– Scratte
                Dec 29, 2021 at 17:32
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.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.