Hello,
i needed a efficient way to sort date something like this:

2016
- 1. Jan
- 22. March
- 1. December

2015
- 3. Feb
- 15. July
- 8. Nov

2014
- 2. Jan
- 5. Jan
- 27. Apr

So i wrote a small patch to use the default views date granularity in a sort handler.
With this patch you are able to sort granular.

Hope it helps some people.

Zeddix

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Zeddix created an issue. See original summary.

capysara’s picture

I couldn't get the patch to apply, so I just manually added the code.

This was exactly what I was looking for and it worked great! Thank you so much!!!

UPDATE: Nope, sorry, this didn't work after more testing. Maybe I've done something wrong... It seems to work for the first group, but the second group is no longer in any kind of order.

2017
January 14, 2017
February 14, 2017
March 2, 2017
April 1, 2017
May 11, 2017

2016
March 1, 2016
February 3, 2016
August 1, 2016
December 1, 2016
January 1, 2016

UPDATE 2: I got it working. I ran into some issues with the date. For example, if I used Jan 1, 2016, and Desc by Year and Asc by Month, it would put it in the wrong order. I changed it to Desc by Year and Asc by Day and it seems right.

Zeddix’s picture

@capysara:
Glad it helped. I however could not reproduce the described issue.
"Desc by Year and Asc by Month" works fine for me. Do you have an minimal example?

What databse do you use?
Looking closer at views_date_sql_format it might affect the outcome of the sorting.
I use mysql on my website.

capysara’s picture

I'm not sure what database I'm using.

I've done some more testing. I wasn't collecting date with time (just day, month, and year), but if I add the time (hours, minutes, seconds), then I can see that 'Friday, January 1, 2016 - 03:45' (or earlier) is sorted to the bottom (incorrectly) and anything '04:00' and later is sorted correctly at the top of the list.

I'll see if I can reproduce with a simple example and write out the steps.

Thanks!

Zeddix’s picture

Okay, that could be a timezone/DST issue then. Assuming you are currently GMT-4.
I will try to reproduce this(hopefully later today) and check in later.

capysara’s picture

I noticed that I had selected "no timezone conversion" in my field_date config. I don't know enough about code to suggestion changes, but it seems that the sort is not respecting that setting and is converting it anyway. (Because it's pulling from the the views sort date for Post Date, which would include timezone conversion?) So, 'Friday, January 1, 2016 - 03:45' is converted to 'Thursday, December 31, 2015 - 22:45' for the sorting, but is still displayed as Jan 1, 2016.

Steps to reproduce:
Fresh install of drupal-7.x-dev
install Views, Date, Devel contrib modules
Apply patch
Add date field to Article content type, set Time zone handling to ‘no time zone conversion’
Generate content, change some content first of the month/first of the year, specifically Jan 1, 2019 before 04:00
Create new view - Show: content, Type: Articles, Unformatted list of fields
Change to Show: fields
Fields: Title is already there
Add date field
Sort by Year (desc)
Sort by Month (or Day) (asc)

And here’s the export of my view:
$view = new view();
$view->name = 'date_views';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Date views';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Date views';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Field: Content: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['label'] = '';
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = FALSE;
/* Field: Content: date */
$handler->display->display_options['fields']['field_date']['id'] = 'field_date';
$handler->display->display_options['fields']['field_date']['table'] = 'field_data_field_date';
$handler->display->display_options['fields']['field_date']['field'] = 'field_date';
$handler->display->display_options['fields']['field_date']['label'] = '';
$handler->display->display_options['fields']['field_date']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_date']['settings'] = array(
'format_type' => 'long',
'custom_date_format' => '',
'fromto' => 'both',
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'show_remaining_days' => 0,
);
/* Sort criterion: Date field by Year */
$handler->display->display_options['sorts']['field_date_value_1']['id'] = 'field_date_value_1';
$handler->display->display_options['sorts']['field_date_value_1']['table'] = 'field_data_field_date';
$handler->display->display_options['sorts']['field_date_value_1']['field'] = 'field_date_value';
$handler->display->display_options['sorts']['field_date_value_1']['ui_name'] = 'Date field by Year';
$handler->display->display_options['sorts']['field_date_value_1']['order'] = 'DESC';
$handler->display->display_options['sorts']['field_date_value_1']['granularity'] = 'year';
/* Sort criterion: Date field by Day */
$handler->display->display_options['sorts']['field_date_value']['id'] = 'field_date_value';
$handler->display->display_options['sorts']['field_date_value']['table'] = 'field_data_field_date';
$handler->display->display_options['sorts']['field_date_value']['field'] = 'field_date_value';
$handler->display->display_options['sorts']['field_date_value']['ui_name'] = 'Date field by Day';
$handler->display->display_options['sorts']['field_date_value']['granularity'] = 'day';
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
'article' => 'article',
);

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'date-views';

Zeddix’s picture

Okay, i updated the code to use the current Date API. This should now handle timezones/databases etc properly.

capysara’s picture

Thank you so much!! That worked great!

gerson.analista’s picture

I used the patch with the new feature on 2 sites and had no problems. Great work, thank you.

DamienMcKenna’s picture

Some small improvements to the file to match Drupal's coding standards.

  • DamienMcKenna committed c8b1953 on 7.x-2.x authored by Zeddix
    Issue #2843660 by Zeddix, DamienMcKenna, capysara, gerson.analista: Add...
DamienMcKenna’s picture

Status: Needs review » Fixed

I felt this minor addition was safe to commit as it didn't touch existing functionality.

Committed, thanks everyone.

joelpittet’s picture

Status: Fixed » Needs work

Some how this broke a view. The pager seems to work but returns no results. I'm in the middle of debugging it.

joelpittet’s picture

A quick code cleanup review:

  1. +++ b/date_views/includes/date_views_sort_handler_simple.inc
    @@ -0,0 +1,38 @@
    +  function query() {
    

    Visibility should be public.

  2. +++ b/date_views/includes/date_views_sort_handler_simple.inc
    @@ -0,0 +1,38 @@
    +  ¶
    ...
    +  ¶
    

    Extra whitespace.

  3. +++ b/date_views/includes/date_views_sort_handler_simple.inc
    @@ -0,0 +1,38 @@
    +    // Set timezones to ensure correct handling
    ...
    +    // Order by newly created field string
    

    Periods at the end of the sentences.

  4. +++ b/date_views/includes/date_views_sort_handler_simple.inc
    @@ -0,0 +1,38 @@
    +    // Get formating based on granularity.
    

    Formatting with two ts.

  5. +++ b/date_views/includes/date_views_sort_handler_simple.inc
    @@ -0,0 +1,38 @@
    +    $formula = $date_handler->sql_format($format,$date_handler->sql_field("$this->table_alias.$this->real_field"));
    

    space before $date_handler argument.

joelpittet’s picture

+++ b/date_views/date_views.module
@@ -396,6 +396,8 @@ function date_views_field_views_data_alter(&$result, $field, $module) {
+          $result[$table][$column]['sort']['handler'] = 'date_views_sort_handler_simple';
+          $result[$table][$column]['sort']['empty field name'] = t('Undated');

This hunk applies the sort handler in all cases. I'd recommend reverting this until it can be tested a bit more.

  • DamienMcKenna committed c96cbf0 on 7.x-2.x
    Revert "Issue #2843660 by Zeddix, DamienMcKenna, capysara, gerson....
DamienMcKenna’s picture

Yeah. Whoops. Thanks joelpittet.

joelpittet’s picture

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_date_field_date_value' in 'order clause'

Seems like the alias doesn't exist in some cases (likely only with granularity of second from the looks of views_handler_sort_date::query()).

The field alias should likely be added or pulled from where it was previously added. I do see an field alias it should use in the result but not sure where that is generated. Plus views truncates long aliases so manually building them will cause breaks regardless.

Sorry a bit sleepy, hopefully that makes sense.