I've been throwing myself at this for a while, apologies if I've missed anything stupidly obvious.

I'm trying to create a table of statistics for content submitted by all users (using Views Aggregator Plus to do the number crunching) and for the most part, it works fine. If I only wanted to show results from all time, it would be perfect.

However, I would like to show the same table but only aggregating content from the past 30 days -- and this is where it gets tricky.

No matter how I configure the filter, if I'm using any of the default content fields (created, updated, etc) with the default date filter options of date in machine readable time or offset from current time, it returns all results.

Date filter settings

I have the SQL query visible so I can see what's going on, and it doesn't appear to be adding the date filter to the query at all.

This is my basic query:

SELECT node_field_data.nid AS nid
{node_field_data} node_field_data
WHERE (node_field_data.status = '1') AND (node_field_data.type IN ('type1', 'type2'))

It doesn't change when I adjust the filter options above.

However, if I use the "Date in YYYY format", which is a filter from a contrib module, it shows in the query and filters like it should:

SELECT node_field_data.nid AS nid
{node_field_data} node_field_data
WHERE (node_field_data.status = '1') AND (node_field_data.type IN ('type1', 'type2')) AND ((YEAR(FROM_UNIXTIME(node_field_data.created)) >= 2021))

The issue doesn't seem to happen with custom date fields. I'm wondering if there's something kooky going on in how Views is handling the dates that are created by the system? When I look at the form display for the content, it shows them as using different "widgets".

Fields created by the system use "Datetime Timestamp", where custom date fields use "Date and Time".

It seeems odd that custom date fields filter beautifully, but default ones don't. And double weird that I would need a contrib module to make use of that information.

I'm running Drupal 8.9.1, everything is up to date. I've tried running from a clean install with the same results. If I run the content through Search API and include "authored on" as an indexed field, the date filtering works but the table aggregation doesn't sort properly.

How do I get Views to recognise and use that data so that I can filter my results down to content posted in X amount of days? Or failing that, what other steps could I take to try and work out where the issue is?

Thanks in advance!

  • 1
    This is interesting. When I test this on one of my views it works as expected. If you add this same filter to the Recent Content view (/admin/structure/views/view/content_recent), does it work there? Does this happen if you disable aggregation? What are you using aggregation for? – sonfd Jun 23 at 12:38
  • It's so weird, right? It does the same thing on the content_recent view. It does it on new views that haven't got aggregation yet. All I need to do to replicate the issue is create a new view showing all published content, set it to show all items (because I want them limited by date not count) and the date filter doesn't work. – sadrii Jun 24 at 4:32
  • Query from the content_recent view: SELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid, users_field_data_node_field_data.uid AS users_field_data_node_field_data_uid FROM {node_field_data} node_field_data INNER JOIN {users_field_data} users_field_data_node_field_data ON node_field_data.uid = users_field_data_node_field_data.uid WHERE ((node_field_data.status = 1 OR (node_field_data.uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1 OR 1 = 1)) AND (node_field_data.type IN ('type1', 'type2')) ORDER BY node_field_data_created DESC – sadrii Jun 24 at 4:32
  • Somehow I fixed this. No idea how, but it's working now. – sadrii Jun 24 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.