Datetime vs Timestamp field

Question

I'm new in MySQL database.

Would you recommend using a datetime or a timestamp field in table creation and why. I'm using MySQL 5.7 and innodb engine.

Thanks

Answer 1

I would use TIMESTAMP for anything that needs to be managed automatically since it supports stuff like ON UPDATE CURRENT_TIMESTAMP or having CURRENT_TIMESTAMP as the default value.

This isn't possible with DATETIME but I do like the format better since you don't need to convert it. So I'd use that for everything else.

If you need a flexible format that can be autogenerated, then you'd probably have to go with TIMESTAMP and convert when needed.

Answer 2

For a creation field I would use a timestamp. You will not get into trouble with timezone stuff this way, which can be pretty tricky.

Timestamp is not usefull for, for instance, birthdays, as you have to deal with the EPOCH aka timestamp '0', which is 1 january 1970. But for creation-time it should not matter.