Problem/Motivation

Views integration for the datetime field type is non-existent.

Proposed resolution

Create filter, sort, and argument plugins for the datetime module.

Remaining tasks

User interface changes

Better options for building views with date fields.

API changes

3 datetime-specific plugins to be added.

Beta phase evaluation

Reference: https://www.drupal.org/core/beta-changes
Issue category Feature
Issue priority Major because the date integration with Views is not complete
Unfrozen changes Unfrozen because it only adds crucial functionality to the date field views integration
Prioritized changes The main goal of this issue is usability

Original report by @KarenS

This is a follow up to #501428: Date and time field type in core. Once the date field is in it needs better Views integration. It needs a filter and argument that have logical settings for date fields rather than the plain string filter and argument provided as the default.

This is currently blocked until the field gets in, but even fixing it in contrib is blocked by #1833296: Unable to alter views data.

Files: 
CommentFileSizeAuthor
#59 date-views-integration-1838242-59.patch31.12 KBjhedstrom
PASSED: [[SimpleTest]]: [PHP 5.4 MySQL] 87,724 pass(es).
[ View ]

Comments

KarenS’s picture

Title:Add better Views integration for date field» Improved Views integration for date field

Fixing title

sun’s picture

Title:Improved Views integration for date field» Improve Views integration for datetime field
Component:views.module» datetime.module
Status:Postponed» Active
Issue tags:+VDC
tim.plunkett’s picture

I'm not going to clobber the major queue by bumping this, but this is pretty darn important.

dawehner’s picture

@karens

It feels like we should bring in the features of the date filter/sort to support datetime values as well,
so something similar to what you started on #241759: Better date filter and continued in the date module?

tim.plunkett’s picture

Assigned:Unassigned» tim.plunkett

I'm going to claim this one for now. We'll see.

tim.plunkett’s picture

Assigned:tim.plunkett» Unassigned

Haven't had the time :(

tim.plunkett’s picture

Issue summary:View changes

Fix the wrong related issue id

klonos’s picture

dawehner’s picture

Assigned:Unassigned» dawehner

Let me try that.

tim.plunkett’s picture

blueminds’s picture

Priority:Normal» Major

I think this is major as the views/datefield combination is not really useful currently. Pls, correct me if I am wrong.

judahtanthony’s picture

Is it just me, or are your dates stored as strings? This has to be the worst data type to store a date in. A string has no sequence and is (most likely) bound to a timezone. Why don't we store it as a DATETIME or at least an INT? It seems pretty significant that you can't create a simple calendar in D8.

field.views.inc: field_views_field_default_views_data()

<?php
   
// Identify likely filters and arguments for each column based on field type.
   
switch ($attributes['type']) {
      case
'int':
      case
'mediumint':
      case
'tinyint':
      case
'bigint':
      case
'serial':
      case
'numeric':
      case
'float':
       
$filter = 'numeric';
       
$argument = 'numeric';
       
$sort = 'standard';
        break;
      case
'text':
      case
'blob':
       
// It does not make sense to sort by blob or text.
       
$allow_sort = FALSE;
      default:
       
$filter = 'string';
       
$argument = 'string';
       
$sort = 'standard';
        break;
    }
?>
jbrown’s picture

dawehner’s picture

Assigned:dawehner» Unassigned

HAHA

jhedstrom’s picture

I wonder if this makes sense to do in core at this time. It seems to me there will still be need for a contrib 'Date' module, to provide things that have not made it into core (end dates immediately come to mind). As such, contrib views integration would most likely need to rework the core views integration.

tim.plunkett’s picture

I think that making date fields at all usable in Views (which they are really not right now) is still a major task that could be committed at any time.
Yes, date.module will need to exist in contrib to provide end dates and the views handlers for that, but we shouldn't give up on handlers for datetime.

webchick’s picture

Agreed on both.

jhedstrom’s picture

Assigned:Unassigned» jhedstrom
Issue summary:View changes

I'll give this a shot.

jhedstrom’s picture

Issue summary:View changes
jhedstrom’s picture

Issue summary:View changes
Status:Active» Needs review
StatusFileSize
new2.89 KB
PASSED: [[SimpleTest]]: [PHP 5.4 MySQL] 82,077 pass(es).
[ View ]

This is a very rough start. It has a 'working' filter plugin. However, I don't think this approach will be cross-database compatible, and to truly get working date filters, we'll need to bring in more of the SQL logic from the Date module (ADDTIME, STR_TO_TIME for MySQL, and the PostgreSQL equivalents).

jhedstrom’s picture

Status:Needs review» Needs work
jhedstrom’s picture

To add an argument plugin, it may be necessary to bring over some of the Date module SQL logic I mentioned above. From D7, adding an argument allowed one to pass in date parts (eg, 2015 for all dates in 2015, or 2015-10 for just October 2015 dates). The resulting SQL (when using MySQL):

WHERE (( (DATE_FORMAT(ADDTIME(STR_TO_DATE(field_data_field_date.field_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-25200)), '%Y-%m') >= '2015-10' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(field_data_field_date.field_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-25200)), '%Y-%m') <= '2015-10') )

jhedstrom’s picture

Status:Needs work» Needs review
StatusFileSize
new6.53 KB
PASSED: [[SimpleTest]]: [PHP 5.4 MySQL] 82,093 pass(es).
[ View ]
new4.24 KB

Actually, looking into how numeric dates are handled, each part is a separate argument type (and core already has the date SQL methods mentioned above, just by different names).

I've started on this approach, and am posting for review now to see what folks think of this direction.

jhedstrom’s picture

StatusFileSize
new8.01 KB
new14.06 KB
PASSED: [[SimpleTest]]: [PHP 5.4 MySQL] 82,049 pass(es).
[ View ]

This adds tests for the new filter plugin.

jhedstrom’s picture

StatusFileSize
new13.48 KB
new22.24 KB
FAILED: [[SimpleTest]]: [PHP 5.4 MySQL] 82,131 pass(es), 1 fail(s), and 0 exception(s).
[ View ]

This adds tests for the 3 argument types (year, month, and day). It also uses format_date() instead of just date() for timezone compatibility.

jhedstrom’s picture

Issue summary:View changes
StatusFileSize
new5.13 KB
new22.76 KB
FAILED: [[SimpleTest]]: [PHP 5.4 MySQL] 82,169 pass(es), 1 fail(s), and 0 exception(s).
[ View ]

This replaces the simple string-comparison in the filter with db-native date functions. I've run the tests on MySQL and PostgreSQL.

MySQL is all green, PostgreSQL fails with:

function to_char(character varying, unknown) does not exist LINE 6: WHERE (( (TO_CHAR(node__field_date.field_date_value, &#039;YYYY&#039;)... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

since the current logic assumes the field being passed in is numeric. I think this can be addressed by overriding the method in the datetime filter handler.

I also added an initial beta-phase evaluation to the summary.

jhedstrom’s picture

Issue summary:View changes
jhedstrom’s picture

Issue summary:View changes
StatusFileSize
new5.55 KB
new25.42 KB
FAILED: [[SimpleTest]]: [PHP 5.4 MySQL] 82,135 pass(es), 1 fail(s), and 0 exception(s).
[ View ]

This gets things working for PostgreSQL, but isn't pretty. The Date module uses the 'EXTRACT' function to grab date partials, but that is a lot of logic to bring in at first glance.

The last submitted patch, 24: date-views-integration-1838242-24.patch, failed testing.

The last submitted patch, 25: date-views-integration-1838242-25.patch, failed testing.

Status:Needs review» Needs work

The last submitted patch, 27: date-views-integration-1838242-27.patch, failed testing.

jhedstrom’s picture

Status:Needs work» Needs review

I'm unsure as to why that one test is failing. It passes locally on both pgsql and mysql.

Setting this back to needs review since feedback on the technical approach is more important at this stage.

jhedstrom’s picture

StatusFileSize
new968 bytes
new25.36 KB
PASSED: [[SimpleTest]]: [PHP 5.4 MySQL] 82,187 pass(es).
[ View ]

This fixes the fail, which was caused by an additional record with a January date being created due to the current date (this was copied from the filter test which needs relative test data).

Sam152’s picture

Came up against this issue. Patch adds good support for filtering with custom date fields. One thing I noticed is that using the table style, dates were not able to be made sortable.

If that is out of scope for this issue +1 to RTBC.

jhedstrom’s picture

Issue summary:View changes

One thing I noticed is that using the table style, dates were not able to be made sortable

This seems to be a larger issue than just dates. I tried adding a numeric field, and that wasn't click sortable either. I'm not able to find an open issue for this though.

This did get me thinking about adding a datetime sort handler though, which would add granularity for sorting.

jhedstrom’s picture

Issue summary:View changes
StatusFileSize
new8.57 KB
new31.65 KB
FAILED: [[SimpleTest]]: [PHP 5.4 MySQL] 84,228 pass(es), 18 fail(s), and 5 exception(s).
[ View ]

This patch adds a sort handler and corresponding tests.

jhedstrom’s picture

I created #2395763: Fields are not 'click sortable' in views for the click sort issue mentioned in #33.

Status:Needs review» Needs work

The last submitted patch, 35: date-views-integration-1838242-35.patch, failed testing.

Status:Needs work» Needs review
Sam152’s picture

In terms of functionality I vote +1 to RTBC, works as advertised with no issues.

Lendude’s picture

Status:Needs review» Needs work
+++ b/core/modules/datetime/datetime.views.inc
@@ -0,0 +1,47 @@
+  $data = field_views_field_default_views_data($field_storage);

This function has been renamed to views_field_default_views_data, this is what causes the tests to fail. Changing the function name turns tests back to green locally.

some nitpicking....

  1. +++ b/core/modules/datetime/src/Plugin/views/filter/Date.php
    @@ -0,0 +1,71 @@
    + * Definition of Drupal\views\Plugin\views\filter\String.

    Change to Contains \ and set to the right class name

  2. +++ b/core/modules/datetime/src/Tests/Views/ArgumentDateTimeTest.php
    @@ -0,0 +1,141 @@
    + * Contains of Drupal\datetime\Tests\Views\ArgumentDateTimeTest.

    'of' too many, lacks leading \

  3. +++ b/core/modules/datetime/src/Tests/Views/FilterDateTimeTest.php
    @@ -0,0 +1,151 @@
    + * Contains of Drupal\datetime\Tests\Views\FilterDateTimeTest.

    of too many, missing leading \

The last submitted patch, 35: date-views-integration-1838242-35.patch, failed testing.

jhedstrom’s picture

Status:Needs work» Needs review
StatusFileSize
new3.07 KB
new31.63 KB
PASSED: [[SimpleTest]]: [PHP 5.4 MySQL] 84,932 pass(es).
[ View ]

Thanks for the feedback @Lendude!

This should fix the fatal error, and code comments as mentioned in #42.

It'd be great to get this in before Beta 5 if it isn't too late.

pivica’s picture

StatusFileSize
new4.16 KB
FAILED: [[SimpleTest]]: [PHP 5.4 MySQL] Unable to apply patch interdiff-1838242-43-44.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
new33.21 KB
PASSED: [[SimpleTest]]: [PHP 5.4 MySQL] 84,921 pass(es).
[ View ]

Patch for #43 works nicely, but what I miss is a fact that end user will see plain text fields for datetime values in views filter config forms and exposed filter form.

Patch #44 is trying to fix this, introducing datetime controls for views datetime filter config popup and exposed datetime filters.
Also one additional settings is addition of 'compare by date' options which alows user to specify does it wants to filter dates by date or also by time part. This is usefull when end user wants to filter all post in one specific day or couple of days and does not wants to select time part.

The last submitted patch, 44: interdiff-1838242-43-44.patch, failed testing.

pivica’s picture

StatusFileSize
new4.34 KB
new33.39 KB
PASSED: [[SimpleTest]]: [PHP 5.4 MySQL] 84,933 pass(es).
[ View ]

OK this form values elements works little strange when they are in config popup and later in views exposed form. New patch against #43, should work better now.

jhedstrom’s picture

I think this direction makes sense to potentially simplify input (not requiring full date+time being entered).

+++ b/core/modules/datetime/src/Plugin/views/filter/Date.php
@@ -0,0 +1,127 @@
+        '#description' => $this->t('Should we compare just date part or also time part?'),

I think this UI text needs to be reworked, taking into account https://www.drupal.org/node/604342.

Berdir’s picture

Yeah :)

I think the way it used to work in 7.x is that you can tick of the granularity for every part, from year to seconds. We should probably look at how that works and steal from there, instead of those two hardcoded "part" thingies ;)

benjy’s picture

Tested this patch, the "An offset from the current time such as "+1 day" or "-2 hours -30 minutes" feature doesn't seem to do anything right now?

jhedstrom’s picture

@Benjy Using an offset is working for me. There's also a test that uses '- 1 day'.

jhedstrom’s picture

@Benjy nevermind, I was still testing with the patch from #43. This is indeed not working in the patch from #46.

jhedstrom’s picture

Thinking about this more, the UI changes from #46 should probably be split into a separate issue which would also address the numeric date plugin UIs provided by Views. The UI from the patch in #43 is identical to that provided by Views.

jhedstrom’s picture

StatusFileSize
new31.63 KB
FAILED: [[SimpleTest]]: [PHP 5.4 MySQL] Failed to run tests: PHP Fatal error encountered during run_tests.sh. See review log for details..
[ View ]

This is a reroll of #43. As I mentioned in #52, let's improve the UI of both the numeric date plugins as well as these new plugins in a follow-up issue so they can have a unified look and feel.

Status:Needs review» Needs work

The last submitted patch, 53: date-views-integration-1838242-53.patch, failed testing.

jhedstrom’s picture

Status:Needs work» Needs review
StatusFileSize
new489 bytes
new31.61 KB
FAILED: [[SimpleTest]]: [PHP 5.4 MySQL] 87,690 pass(es), 15 fail(s), and 3 exception(s).
[ View ]

Status:Needs review» Needs work

The last submitted patch, 55: date-views-integration-1838242-55.patch, failed testing.

jhedstrom’s picture

Status:Needs work» Needs review
StatusFileSize
new2.65 KB
new31.13 KB
FAILED: [[SimpleTest]]: [PHP 5.4 MySQL] 87,711 pass(es), 2 fail(s), and 0 exception(s).
[ View ]

Status:Needs review» Needs work

The last submitted patch, 57: date-views-integration-1838242-57.patch, failed testing.

jhedstrom’s picture

Status:Needs work» Needs review
StatusFileSize
new1.26 KB
new31.12 KB
PASSED: [[SimpleTest]]: [PHP 5.4 MySQL] 87,724 pass(es).
[ View ]

More schema fixes.