Add constant for database date format

[Rarst]

A lot of core Date/Time code is dealing with Y-m-d H:i:s format, as stored for posts and other objects in database.

This is effectively a so-called "magic" string, which is not self-explanatory and prone to user error if typed by hand (I messed up i with m for minutes more than once probably).

Since there is no upstream PHP constant for this format, I suggest we introduce one in core and use it in place of magic string:

define( 'WP_DATE_MYSQL', 'Y-m-d H:i:s' );

Not absolutely sure about the name, I used DATE_MYSQL as class constant before, but WP_DATE_DATABASE might be more appropriately generic. To my knowledge there isn't a specific ISO or RFC designation for such format.

If there is agreement on the need and the name I'll work on a patch for replacement and switch from strings in core.

[remcotolsma]

I have developed custom WordPress solutions that used DATE and DATETIME(6) types in custom database tables. I don't think WordPress is using these MySQL types currently. But the name WP_DATE_MYSQL could be confusing, for me it makes more sense that that constant contains the 'Y-m-d' notation. Could be improved by using something like this:

• define( 'WP_DATE_MYSQL_DATE', 'Y-m-d' );
• define( 'WP_DATE_MYSQL_DATETIME', 'Y-m-d H:i:s' );
• define( 'WP_DATE_MYSQL_DATETIME_6', 'Y-m-d H:i:s.u' );

but WP_DATE_DATABASE might be more appropriately generic.

Maybe other database systems do accept a ISO 8601 date in queries?

• ​https://dev.mysql.com/doc/refman/8.0/en/datetime.html
• ​https://github.com/php/php-src/pull/3690

[Rarst]

Thanks for feedback, clicked through the references. Don't want to get the name too long probably.

Since DATETIME is upstream in ANSI SQL, perhaps WP_DATE_SQL_DATETIME.

I don't think there is a strong case constants that aren't used by core code/schema.