Enhancement: Add datetime column to options table.

[nerrad]

Proposal

The options table in WordPress is a great key/value storage option for a wide variety of different data used by core and plugins. One improvement that would increase its utility for faster time based queries on data stored there is to add a DATETIME column.

Some examples where this benefit could be realized:

Example 1: Transient storage.

Currently, when there is no object-cache in use, transients are stored to the wp_options table. However, for each transient there are two records. One for the actual key/value pair and then one for any timestamp set as the transient expiry. Having a datetime column would allow the transient to always only consist of one record and thus make any queries interacting with transients much simpler.

Example 2: Arbitrary plugin data using the options table for its own scheduled tasks.

A lot of plugins are using the transient system wrong because it's not intended for indicating minimum age. Having a datetime column would provide the database schema in WordPress core that allows for plugins to implement their own "minimum/maximum age" apis.

Example 3: Tracking creation/modification times.

Having a datetime column would allow for indicating when a key/value pair was created and/or modified which could be useful for plugins that have need to do so.

Example 4: Scheduled settings/options.

Having a datetime column could allow for scheduled changes with a sites configuration and thus more advanced previews/site preparation, (think adding scheduled changes to site title, or site description via the customizer). Having a datetime column makes such schedules simpler to implement.

Implementation

Schema

option_date datetime NOT NULL default '0000-00-00 00:00:00'

Iterations:

1. Add the column and modify options api to expose the new column to queries (get_option, update_option, site option functions etc).
2. Convert transient API to implement new option_date column for setting expiries when object-cache is not in use.

Who and When

I'd be willing to spearhead putting some patches together and getting the initial code going but before I invest some time in this I'm just testing the waters to see if this is even something that would be considered/welcomed for core. I'm not aware of any potential conflicts this may pose with the purpose for the option table but if there are any I'm sure I'll find out!

I definitely don't see this as going in 4.6 but it might have potential for 4.7 if work begins fairly soon.

[MikeHansenMe]

Let's see if we can get some feedback from a lead dev on this. This would in fact be a big change and would need to be thoroughly unit tested.