Recommendations for storing dates in the database

Dates are a “pain” both in terms of use and in terms of debugging. I do not take into account local simple site. Few people know how to "cook" dates in the correct mode.

The same Yii - there all dates in the database are written generally in INT. That is, not 2019-03-27 13:43:19, but not the readable number 1829189871. Although there are field types and DATE, and DATETIME, but no, everyone uses INT intently and does not even ask themselves why INT?

It went from afar when it was believed that MySQL with the DATE field was slower than with INT. DATE looks like text, and INT is a number, and operations are always faster with numbers, and you can’t argue. But the whole mistake is that both DATE and DATETIME are also stored in the database in INT format.

What other problem is with INT instead of date ... Number 0 is 1970-01-01 00:00:00, that is, UNIXTIME start time. And you can’t set a date less than this via INT, unless there are just negative values, which will look very strange in general.

I also want to mention that if the "date is unknown", use NULL, and not the value 0001-01-01 00:00:00. In general, the "value" of NULL is a separate topic. The problem with a null date is that it (first) must be explicitly checked for NULL, and (second) if the date is zero, the date functions in PHP do not return NULL, but the current date.

When the project is in different cities, the problem of time zones arises. As a result, when the date is stored in the database, it is not clear - this is the date for the city, for the server, for UTC, or some other. By default, either the DBMS belt or the PHP belt. And “oh, problems” when the DBMS and PHP have different time zones.

Summarizing all of the above, we recommend:

  • All dates in the database should be stored in either DATE or DATETIME format.
  • For "zero date" use 0001-01-01 00:00:00, not NULL.
  • Store dates in UTC only and convert them from the time zone of PHP or JS to UTC.
Top