In Drupal 7, as it supports different databases, modules which run queries containing date conversions and comparisons (Views, Date and others) face with a number of bugs connected to different SQL syntax. At the same time these modules have a lot of almost identical code related to DB date handling. Date functions are a part of SQL and I think they should not be handled by modules. We need a kind of date abstraction layer within database layer, which will contain date formatting, conversion, and extraction routines. I don't know if current DB layer can be changed too much, but the idea can become a roadmap for Drupal 8.

Suggested functions can include:
- date formatting function;
- date diff;
- date addition and subtraction;
- date parts extraction;
- date comparison (however I'm not sure if we really need this one);
- timezone handling.

Functions can operate on PHP DateTime, DateTimeZone, DateInterval objects.

Comments

lmou’s picture

So in a nutshell: the use of my own tables and data in Drupal , will be made easier in Drupal 8.x?
Now, in Drupal 7.x, I can launch a query in a window, but if something goes wrong in this routine, I can forget my drupal-website, nothing works anymore. I had it ones, and had to remove the newliy created node directly in the database to bring my Site to live again.

peterx’s picture

One reason for using a different date time format is to have a date work past the Unix Y2038 bug. Here are the options in PostgreSQL: http://www.postgresql.org/docs/8.2/static/datatype-datetime.html
MySQL: http://dev.mysql.com/doc/refman/5.6/en/datetime.html
There is some overlap of function but not an exact match. It would be nice to tell D8 I want a 4 digit year then have D8 select, at the schema level, the smallest format from each database to fit 4 digit years. D8 could format all the input fields for 4 digit years and format all output displays for 4 digits.

d.novikov’s picture

Let me show an example of what I'm speaking about. Compare these two pieces of code from Date and Views modules, respectively:

function sql_format($format, $field) {
      switch ($this->db_type) {
        case 'mysql':
        case 'mysqli':
        $replace = array(
          'Y' => '%Y',
          'y' => '%y',
          'M' => '%b',
          'm' => '%m',
          'n' => '%c',
          'F' => '%M',
          'D' => '%a',
          'd' => '%d',
          'l' => '%W',
          'j' => '%e',
          'W' => '%v',
          'H' => '%H',
          'h' => '%h',
          'i' => '%i',
          's' => '%s',
          'A' => '%p',
          '\WW' => 'W%U',
          );
        $format = strtr($format, $replace);
        return "DATE_FORMAT($field, '$format')";
function views_date_sql_format($format, $field, $field_type = 'int', $set_offset = NULL) {
  $db_type = db_driver();
  $field = views_date_sql_field($field, $field_type, $set_offset);
  switch ($db_type) {
    case 'mysql':
    case 'mysqli':
      $replace = array(
        'Y' => '%Y',
        'y' => '%y',
        'M' => '%b',
        'm' => '%m',
        'n' => '%c',
        'F' => '%M',
        'D' => '%a',
        'd' => '%d',
        'l' => '%W',
        'j' => '%e',
        'W' => '%v',
        'H' => '%H',
        'h' => '%h',
        'i' => '%i',
        's' => '%s',
        'A' => '%p',
        );
      $format = strtr($format, $replace);
      return "DATE_FORMAT($field, '$format')";

As you see, the code is almost identical. There are also similar pieces for pgsql, sqlite and mssql databases. Will it be better if we move such repeating functionality to database layer?

peterx’s picture

Yes, sharing the date formatting simplifies things and improves interoperability. What I am thinking about is having the format independent of the underlying date field. The code you quote appears to be for the standard Unix timestamp. The Views code specifies int. If I have code for a "long date", could I access the same replacement array?

The sql_format function could work for different types of date fields. My long date requirement would need only the specification of Y instead of y.

The views_date_sql_format is built for int fields. If views_date_sql_format could be rewritten to use sql_format for the replacement part, sql_format could be kept generic and applied to other types of date fields. If the change went the other way, toward the Views approach, the code could not be reused for other date formats.

When you simplify the existing code, it is easier to add extra features. If the date handling was in a class, the class could be extended for long dates and other requirements.

Whatever is decided on dates, I would like to be able to add a module and make use of the replace table without having to include it in my code or to know what database is used.

d.novikov’s picture

Speaking about the code samples, DATE_FORMAT() accepts date in different formats and returns a string. But it is just for demonstration, that we have very similar pieces of code in different modules, and these pieces can be moved to database abstraction layer, as they contain elements of SQL.

d.novikov’s picture

I created a sandbox project for this: http://drupal.org/sandbox/d.novikov/1762750 (no more avail)

Cameron Tod’s picture

Status: Active » Postponed
mgifford’s picture

Issue summary: View changes
Status: Postponed » Active

Why was this postponed?

d.novikov’s picture

Hey @mgifford,

If core contributors confirm this is still actual and can be included to the core, I'm ready to work on this and provide a patch or help other way.

mgifford’s picture

Thanks @d.novikov - really would be good to know this is still relevant before working on it. I'm not familiar enough with this part of Core to say.

peterx’s picture

The next step is probably to backport any improvements made in D8, if any. D8 switched from alpha to beta which means the API should be stable. Did they include expanded date processing?

A quick test of D8 shows two date formats, date+time and date without time. Both are stored as varchar 20. Hmmm? If we did something smart for D7, it would have to be converted back to a string in D8. Perhaps this issue could lead to a backport of the varchar 20 date from D8.

mgifford’s picture

I don't think it's been fixed in D8 yet. It may not still actively be a problem in D8. I don't know.

Version: 8.0.x-dev » 8.1.x-dev

Drupal 8.0.6 was released on April 6 and is the final bugfix release for the Drupal 8.0.x series. Drupal 8.0.x will not receive any further development aside from security fixes. Drupal 8.1.0-rc1 is now available and sites should prepare to update to 8.1.0.

Bug reports should be targeted against the 8.1.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.1.x-dev » 8.2.x-dev

Drupal 8.1.9 was released on September 7 and is the final bugfix release for the Drupal 8.1.x series. Drupal 8.1.x will not receive any further development aside from security fixes. Drupal 8.2.0-rc1 is now available and sites should prepare to upgrade to 8.2.0.

Bug reports should be targeted against the 8.2.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.6 was released on February 1, 2017 and is the final full bugfix release for the Drupal 8.2.x series. Drupal 8.2.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.3.0 on April 5, 2017. (Drupal 8.3.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.3.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.6 was released on August 2, 2017 and is the final full bugfix release for the Drupal 8.3.x series. Drupal 8.3.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.4.0 on October 4, 2017. (Drupal 8.4.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.4.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.4 was released on January 3, 2018 and is the final full bugfix release for the Drupal 8.4.x series. Drupal 8.4.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.5.0 on March 7, 2018. (Drupal 8.5.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.5.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.6 was released on August 1, 2018 and is the final bugfix release for the Drupal 8.5.x series. Drupal 8.5.x will not receive any further development aside from security fixes. Sites should prepare to update to 8.6.0 on September 5, 2018. (Drupal 8.6.0-rc1 is available for testing.)

Bug reports should be targeted against the 8.6.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.6.x-dev » 8.8.x-dev

Drupal 8.6.x will not receive any further development aside from security fixes. Bug reports should be targeted against the 8.8.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.9.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.7 was released on June 3, 2020 and is the final full bugfix release for the Drupal 8.8.x series. Drupal 8.8.x will not receive any further development aside from security fixes. Sites should prepare to update to Drupal 8.9.0 or Drupal 9.0.0 for ongoing support.

Bug reports should be targeted against the 8.9.x-dev branch from now on, and new development or disruptive changes should be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.9.x-dev » 9.2.x-dev

Drupal 8 is end-of-life as of November 17, 2021. There will not be further changes made to Drupal 8. Bugfixes are now made to the 9.3.x and higher branches only. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.2.x-dev » 9.3.x-dev

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.15 was released on June 1st, 2022 and is the final full bugfix release for the Drupal 9.3.x series. Drupal 9.3.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.4.x-dev branch from now on, and new development or disruptive changes should be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.9 was released on December 7, 2022 and is the final full bugfix release for the Drupal 9.4.x series. Drupal 9.4.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.5.x-dev branch from now on, and new development or disruptive changes should be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.