Early Bird Registration for DrupalCon Portland 2024 is open! Register by 23:59 PST on 31 March 2024, to get $100 off your ticket.
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
Comment | File | Size | Author |
---|---|---|---|
#10 | date2843660-10.patch | 2.66 KB | DamienMcKenna |
| |||
#10 | date2843660-10.interdiff.txt | 2.13 KB | DamienMcKenna |
#7 | add_granularity_to-2843660-7.patch | 2.65 KB | Zeddix |
added-granularity-to-date_views-sort-handler.patch | 2.97 KB | Zeddix | |
Comments
Comment #2
capysara CreditAttribution: capysara commentedI 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.
Comment #3
Zeddix CreditAttribution: Zeddix as a volunteer commented@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.
Comment #4
capysara CreditAttribution: capysara commentedI'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!
Comment #5
Zeddix CreditAttribution: Zeddix as a volunteer commentedOkay, 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.
Comment #6
capysara CreditAttribution: capysara commentedI 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';
Comment #7
Zeddix CreditAttribution: Zeddix as a volunteer commentedOkay, i updated the code to use the current Date API. This should now handle timezones/databases etc properly.
Comment #8
capysara CreditAttribution: capysara commentedThank you so much!! That worked great!
Comment #9
gerson.analista CreditAttribution: gerson.analista as a volunteer commentedI used the patch with the new feature on 2 sites and had no problems. Great work, thank you.
Comment #10
DamienMcKennaSome small improvements to the file to match Drupal's coding standards.
Comment #12
DamienMcKennaI felt this minor addition was safe to commit as it didn't touch existing functionality.
Committed, thanks everyone.
Comment #13
joelpittetSome how this broke a view. The pager seems to work but returns no results. I'm in the middle of debugging it.
Comment #14
joelpittetA quick code cleanup review:
Visibility should be public.
Extra whitespace.
Periods at the end of the sentences.
Formatting with two ts.
space before $date_handler argument.
Comment #15
joelpittetThis hunk applies the sort handler in all cases. I'd recommend reverting this until it can be tested a bit more.
Comment #17
DamienMcKennaYeah. Whoops. Thanks joelpittet.
Comment #18
joelpittetSQLSTATE[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.