[wp-trac] [WordPress Trac] #36655: Enhancement: Add datetime column to options table.

[WordPress Trac]

#36655: Enhancement: Add datetime column to options table.
--------------------------------+-----------------------------
 Reporter:  nerrad              |      Owner:
     Type:  enhancement         |     Status:  new
 Priority:  normal              |  Milestone:  Awaiting Review
Component:  Options, Meta APIs  |    Version:
 Severity:  normal              |   Keywords:
  Focuses:                      |
--------------------------------+-----------------------------
 == 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.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/36655>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform