What are the Best Practices for Database DateTime Storage?

Dates are datetimes are common values that coders are going to want to save. But what is the ideal format?

With MySQL, you have the options of Date (default "0000-00-00"), DateTime (default "0000-00-00 00:00:00"), and even Year ("default "0000").

Any of these should fit your purposes for storing recent data. The only limits are that Date and DateTime cannot store years older than 1000, and Year has even smaller range of valid values.

There are advantages to storing dates this way: timezones are built-in to MySQL which can be applied or unapplied easily, the data is fit into as few bits as possible to get you the best storage, you can always sort by using the "greater than" or "lesser than" operators, etc..

Some people store this value as a string. But that is quite a bit more data to hold into the system, and it will accept invalid date/datetime values. This should be avoided.

Some people store this value as an integer, using the epoch time schema. But epoch time is timezone-less, and there will be no way for MySQL to be able to adjust or unadjust this time for timezones. This should be avoided.

Use MySQL's Date or DateTime.