2

So, I am using Drupal 7.19 and Views 3.5. For some reason, I can not group by a date field no matter what!

I created a simple view that displays the number of nodes that have been published per month. As such, I selected the Nid and Post date fields, enable aggregation, count on Nid and group on Post date.

No grouping. There is a record being pulled for every single node in the database, as if the grouping is being completely ignored.

If you run the SQL statement Views generates on the database, you also get a complete result set but that is obviously because the UNIX time stamps themselves are different, if even my a second. That's expected. How can I get Views to group by a formatted value, such as a Month, Year, etc, to avoid this problem?

I tried everything suggested here and elsewhere - I already had the RDF module disabled, emptied cache, added a new date format, strip white space from the date field ,I even disabled the Date module for fear it was conflicting with it.

This is driving me absolutely crazy! Would appreciate any suggestions. Thanks.

Improve this question

4 Answers 4

Reset to default
2

You cannot group on fields which are not equal. The post date is a datetime field in unix time format (number of seconds since 1/1/1970). Unless you have several nodes created at exactly the same moment in time, you will not be able to group on this field.

You are going to have to group on a derivative. Look at the way the standard Archive view does it. The query is 

SELECT DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL -14400 SECOND), '%Y%m') AS created_year_month, COUNT(node.nid) AS num_records
FROM 
{node} node
WHERE (( (node.status = '1') ))
GROUP BY created_year_month
ORDER BY created_year_month DESC
LIMIT 30 OFFSET 0

This puts the created date in the form YYYYMM. You are going to have to rewrite the field and group on the rewritten results. That way you are only dealing with the year and month portions of the date.

The archive view does this with a pre-defined 'bogus field' called created_year_month. This field is available as an argument (only) because it is defined as such in the default views handlers.

To achieve a similar result in your own view, you can either clone the Archive view and try to alter it for your purposes, or you are going to have to write your own 'field' handlers to expose the field in its desired format for your view. Another option is to use the Views Date Format SQL module, which does exactly this.

Improve this answer
7
  • I am not sure I understand what you mean by I cannot "group on fields which are not equal". I looked at the Archive view and it does not select any fields. So I am not sure how it is similar to what I need. Can you please give me an example?
    – 9ee1
    Commented May 25, 2013 at 7:50
  • @9ee1 It means you cannot group on fields with different values. Just change date format to custom "Y m" so the value of post date will be the same for nodes created in same month.
    – Jack-PL
    Commented May 25, 2013 at 14:04
  • 1
    @Jack-PL - It won't work. The date formatting has to happen in the query, not after the information is retrieved. What will happen with the "Ym" formatting as you suggest is the Date Posted will be retrieved in unix format, aggregation will fail, then the display will be formatted. This is why the Archive view uses a contextual filter to achieve the result. One of the available filters is Content: Created year + month (defined in views/modules/node.views.inc).
    – Triskelion
    Commented May 25, 2013 at 16:02
  • Thanks for your clarification Triskelion. I didn't understand what cannot group by unequal fields meant since, quite honestly the statement makes no sense - Of course a field might have different repeated values in the overall set, hence the point of the grouping :).But you are right, the formatting has to happen in the query. I did find a module, drupal.org/project/views_date_format_sql, that does exactly what you suggested. Saved me some time writing the code myself. Thanks again.
    – 9ee1
    Commented May 25, 2013 at 21:30
  • In your question you alluded to it: the UNIX time stamps themselves are different. You need several records with the same value to group on that value. That is why the grouping fails on Posted Date. The granularity of the field is 1 second. You need to derive a value where the granularity is one month to group by month. Glad you found a solution. Good luck :-)
    – Triskelion
    Commented May 25, 2013 at 21:42
3

To group on a date field just copy the contextual filter from the default archive view. No need for aggregation to be turned on. Settings shown in pic:

enter image description here

Improve this answer
3
  • What if I don't want the dates to be links?
    – nexus_6
    Commented Jan 29, 2015 at 22:46
  • This is...genius.
    – YPCrumble
    Commented Jan 18, 2017 at 22:12
  • This is a views attachment, not a view - so you cannot do things like turn this into a jump menu
    – frazras
    Commented May 26, 2017 at 16:08
0

I've just stumbled across this problem myself: not being able to group on the 'Posted Date' field. I simply created another date field, used granularity of day then this automatically collects the published date as nodes are created without touching it. Everything working fine now..

Improve this answer
0

If you are using aggregation and want to sort in order of when a node was created, just sort by node id. Each time a new node in drupal is created it alway increments up using the last node created. It does not matter if a previous node was deleted, whatever the last node id... drupal will add 1.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.