How to be friend with mysql timestamp

TIMESTAMP data type is a special column. Internally, it stores data as unix int and shows you in timezone you have set your mysql.

Try not to insert explicitly

INSERT INTO `my_table`(`title`, `user_id`, `created_at`, `updated_at`) VALUES ('my title', 1, null, null);

or

INSERT INTO `my_table`(`title`, `user_id`) VALUES ('my title', 1);

or (this is not recommended)

INSERT INTO `my_table`(`title`, `user_id`, `created_at`, `updated_at`) VALUES ('my title', 1, NOW(), NOW());

So if you have only this type of queries, you are good to go. You can change mysql timezone every day, but your data will be the same. All because mysql stores it in unix.

If you are using timestamp explicitly, you have work to do

INSERT INTO `my_table`(`title`, `user_id`, `created_at`, `updated_at`) VALUES ('my title', 1, '2020-12-25 12:15:17', '2020-12-25 12:15:17');

Okay. Here what mysql will do. mysql will convert your value ‘2020-20-25 12:15:17’ to unix using mysql timezone setting. Before using such statements, you must set timezone in mysql same as in your app. Then it’s safe to do this.

But if your value timezone is not the same as mysql timezone, you are in trouble as your data timestamps will be offset.

Big php forums (phpbb, vbulletin, xenforo) stores datetime as unix int because they offer their soft to clients that know nothing about timestamp column and because forum users have ability to change timezone, which is very convenient to have simple int.