Add granularity to Date Field filter & sort criteria in Views for anniversary type events

+1, I also would love this feature.
A workaround would be to use computed field to extract month and day from a cck date field, but I don't know how to do this. Any help would be great.

No new features in D5, moving feature requests to D6.

I found out how to achieve the workaround I suggested in #1. Hope this is useful for someone else.

How to create a 'Next Birthdays' View, showing a list of users sorted by day and month of birth, disregarding the year:

- Add a cck computed field to your Profile called next_birthday, which will caculate the date of user's next birthday based on his date of birth (field_date_of_birth). If the day and month of birth is equal or greater than today, next birthday will be the day and month of birth in the current year. Otherwise, next birthday will be the day and month of birth in the next year.

- Put the following code in the 'Computed Code':

<?php
if ($node->field_date_of_birth[0]['value']):
    $birthunix = strtotime($node->field_date_of_birth[0]['value']);
    $daybirth = date('d', $birthunix);
    $monthbirth = date('m', $birthunix);
    $todayunix = mktime(0, 0, 0, date('m')  , date('d'), date('Y'));
    $yearbirthday = date('Y', $todayunix);
    $nextbirthdayunix = mktime(0, 0, 0, date($monthbirth)  , date($daybirth), date($yearbirthday));
    if ($nextbirthdayunix < $todayunix) {
        $yearbirthday = $yearbirthday + 1;
        $nextbirthdayunix = mktime(0, 0, 0, date($monthbirth)  , date($daybirth), date($yearbirthday));
    }
    $node_field[0]['value'] = $nextbirthdayunix;
else:
    $node_field[0]['value'] = 0;
endif; 
?>

- Check 'Store using the database settings below'; Date type: 'varchar'; Data lenght: 128 (I really don't know exactly what to put here, but 128 worked!); Check 'Sortable'.

- You will have to resave all already existing Profile Nodes for the new field to be calculated. To do this, put the code below in the body of a new node with PHP code filter:

<?php
$res = db_query("SELECT n.nid FROM {node} n WHERE n.type = 'profile'");
print '<ol>';
while ($n = db_fetch_object($res)) {
  node_save(&$n);
  print '<li>'.l($n->title, 'node/'.$n->nid).'</li>';
}
print '</ol>';
?>

- You can then sort your View by Computed:Next Birthday. Filter the View by 'date of birth:year is greater than 1900', to exclude the Profiles in which users did not fill in the field.

Suggestions on how to improve the snippet above would be appreciated.

Is that the only way doing this in 6.x ?

subscribe, for Today in History (On this day ... ) functionality.

This snippet works, until time passes, and the Next Birthday date stored in the database passes. To keep this working, you need to recaculate the calculated values in the profiles.

I tried to remove the storage of the computed field, but then it is not available for my views.

The storage feature would work if you could execute the code in the profile update page as part of the cron tasks. Is this possible?

So my workaround is to run my Next Birthday Update code via the page I created as if it is a cron job - which it is now! I run it once a day to update the upcoming birthday for all users. Seems like a waste of resources, but it works for now.

subscribe

Yes, wylbur, you are right. The profiles must be saved regularly to keep the next birthday info updated.
Could you explain how you run your Next Birthday Update code as cron job?

subscribing

subscribing

Hello all together,

here is the cron job (as a seperate module) executed once a day (see also: http://drupal.org/node/435992):

function mymodule_cron() {
  // today's date as of midnight, exp. 04/16/09 12:00:00 am
  define('TODAY', mktime(0, 0, 0, date('n'), date('j'), date('Y'), 0));

  if (variable_get('daily_cron', TODAY) != TODAY) {
    variable_set('daily_cron', TODAY);
   
    // preserve current user object
$current_user = $user;

// switch user from anonymous to another uid
$user = user_load(array('uid' => 1));

// do whatever else you need
// ==> script which selects all relevant nodes and calls node_save for each node 
$res = db_query("SELECT n.nid FROM {node} n WHERE n.type = 'profile'");
$i=0;
while ($n = db_fetch_object($res)) {
  node_save(&$n);
  $i++;
}
$updateMessage = "updated ".$i." profile nodes";
print($updateMessage);
// maybe some other logging => watchdog?


// restore current user object before exiting
$user = $current_user;
  }
}

I haven't tested it up to now, but I will do it, because I have exact the same situation/problem. Maybe next week if I have the time ...
I hope this will help!

Best regards,

Chris

subscribing

Hi everybody,

since there were a number of people asking for this, including myself and since I wanted to get things done and since the actual code is really not that complicated, I sat down and wrote a small module that (hopefully) does what has been requested here. So I proudly present a prerelease version of a thing that I call "datesort" module for now.

It is an enhanced duplicate of the views_handler_sort_date. Besides fixing a bug when not used with datestamp (a.k.a. unix date stamp) field, it adds a birthday mode and as a further addition a round robin offset.

• The birthday mode does the sorting based on the DAY OF YEAR function provided by the DB. This way we get the sorting gets independent of the year portion in the date
• On top of that you can enter round robin offset. If this field not empty and set to 0, you will get the next birthday from today first in the sorting and the most recent birthday last in the sorting.
  
  The offset can be used to shift the today value back in time. So with an offset of 7 you get the birthdays of last week shown first in the sorting.

I have not (yet) checked in this code to make it available as project because I think it should be either part of the date module or cck or views. I'd be willing to check it in, maybe with a slightly different name – i.e. "birthdaysort" if nobody is willing to integrate this.
The code could directly be used to replace views_handler_sort_date since it originates from this class.
The only other thing that needs work is to add code that will use this handler instead of content_handler_sort in CCK date fields

I marked the following issues as duplicates of this one:
#468456: Views filter separately for each component of the date field (YYYY) (MM) (DD)
#410856: Date argument to get same day and month date but different year
#448628: Exposed Date Views filter could ignore year
#219794: Allow dates without years.

By anniversary-type events, I mean events that happened in the past, but are celebrated or recognized on a yearly (or monthly or weekly or daily or hourly?) basis.
Examples include: Birthdays, historical events, wedding anniversaries, other anniversaries, etc.

I have not fully gone through all the comments & code above, but one thing that might be possible is to use a cck computed field(s) for this purpose (http://drupal.org/project/computed_field).
A computed field could extract month & day information from the date and store those values. Then you can use views to easily filter/sort on the computed fields instead of the full date.

Integration into the calendar views/grids might be another issue... dabtek's method sort of gets around that, but there are drawbacks as noted above.

Since I'm not a native english speaker, I wasn't sure how to call the thing. I like your term "aniversary-type" and it is exactly what I proposed in the module attached in #13. Replace birthday with aniversary-type and I think this is what you want.

I haven't posted this as a separate module since I think it should be somehow integrated into the date module.

Also note that I have not checked the results with Calendar View (yet).

Hello skybow, does your datesort module works with Drupal 5.x (the original version this issue was created)?

I started with D6 and have never used D5, so I can't tell.
If the Views API for D5 is similar to the one for D6, then it should be possible to backport the filter though.

Hi, excelent module (datesort). Was exactly what i need!!

why don't you create a project at drupal.org with these module?

cheers!

--
Luciano Rossi (lukio)

Hi lukio,

I have not posted it as module since I think that this belongs into either CCK, Views or probably best into the Date module in the first place. Especially since the net amount of code that does the logic is rather small compared to the overhead of replacing the Views' sort handler. I'm not sure how much overhead there is to having an awful lot of modules for rather tiny pieces of functionality, and maybe it is not that much in a production environment where you don't have to change the module settings a lot. But I experienced rather long waiting time when I need to enable/disable a module, the more modules I have.

But true, I'm surprised that none of the responsible persons made even a note that this showed up on their radar.
I'll give it another one or two weeks until I will make this a drupal project.

Cheers

Great module (datesort). But what i need is to show "birthdays, anniversaries" only for today's date (for example all content for 25.11). Is it possible to achieve this with this module?

It's been a while, since I last looked at the code. However, from reading my own comments, I think you can't because I only defined a sort handler. What you need is a filter that is able to ignore the year portion of a date.

I will think about adding this to my code, but right now I'm rather busy, so please don't expect this to happen very soon.

Thank you for quick response. Yes it is a filter what i need.

@arlinsandbulte, thanks, that's a really good solution.

Hope to see inside the Views module in the future.

subscribing

subscribing

+subscribing

any news ?:)

I could definitely use the ability to filter by month only! This is vaguely similar to the issue I just posted about the Optional option not working for exposed Date filters: #717682: Views filter "optional" option not working?

@OliverColeman:
could you please export that view here?
I would like to know if this matches my request or if I have to describe it in a more detailled way.
Thanks in advance.

@R.Hendel
I no longer have the view to export as I'd actually like it to work as I've implemented it for now using the module in #13. Basically what I've got is some profile nodes with a date of birth (year/month/day), and I want a view with a Date filter that filters by the month only and ignores the year, thus listing all birthdays that occur in that month.

I think what's different in my case as opposed to what most people here want is that I want to expose the filter and have it default to the current month (with no year field displayed (either that or just have the year field default to an "any" option and then I can hide it with CSS)).

However, for both what I want and what I think most people here want, this would only work if it was possible to select relative values separately for year, month and day in the filter settings form (for example leaving year and month blank and selecting "now" for the day)

The way I've implemented it at the moment, with the datesort module in #13, is that it displays the first 50 profiles who's birthdays occur after 7 days ago (the offset setting is set to -7). Let me know if you want me to export that. This view is for administrative purposes only so it doesn't matter that it displays a few extra profiles with birthdays in the next month or two (and in fact it displays a pager so you could see all profiles with birthdays occurring throughout the year).

Hi Oliver,
thanks for your detailled answer.
In fact we have another and more complicated request: We want to have one single calendar (using calendar module) which should display events AND birthdays. This is very complicated as there is just one single argument field which matches the records. Of course this calendar should display birthdays annually and not only the users, which are born today :-)

@all:
We are going to develop a special module for this. I will leave a comment here, as soon as it is submittet - hopefully in two weeks. If I will forget this, please remember me :-)

Best regards,
Ralf

Any updates?

I would love this feature!

Up til now I just misused the 'node: Post date' field. View's style allows you to group by field and the Node: Post date field can be configured to output a custom date format (for example year only). This is a wonderful way of creating archives of nodes, sorted by year.

Today I realized that this is a rather dirty way of doing things and that I should probably use the Date module for 'real' dates and Node: Post date for its intended use. Unfortunately Date doesn't seem to offer the option to output a custom date-format in Views...

Sorry, I found the solution about 10 minutes after posting this. I had to add a new format (year only) to the datetime field I created for the content type and set that as default display.

tweaking title to make it easier to search for
(I just had a hell of a time finding it...)

(For anyone who stumbles across this while searching...)

re: comment #14....
A computed field might be useful for sort, but not for filter. The computer field would end up being an integer, and the Views numeric filter doesn't support dynamic values as criteria.

ex. I might have a computed field made up of the month concatenated with the day, like "520" (May 20th), but the numeric filter only support hard coded values, so I couldn't pass in, say date("nd") or date("nd") + 5 or whatever.

You can, however, use computed field + an argument: provide a default value + PHP code and you could return date("n") but that's only good if you want to get a list of birthdays or events that would have a given month, or a particular day (use return date("nd") for a day value).

(And for anybody who knows more about Date/Views internal workings than I do, the interface for the Date filter is in date/includes/date_api_filter_handler.inc ...I don't know enough about creating custom filters to edit this class and add in a "birthday mode" similar to the datesort mini module)

Any solutions/news about filtering only by month or day or year? Thanks.

zoo

What I did in order to sort by birthdates is to hook into views api and change the sorting (assuming my module is mymoudle, the view is employees, the display im after is block_2 and the birthday field is called field_bd):

function mymodule_views_query_alter(&$view, &$query){
if ($view->name == 'employees' && $view->current_display == 'block_2') {
$query->orderby[0]= "((DAYOFYEAR(node_data_field_bd.field_bd_value) - DAYOFYEAR(NOW()) + 365 ) % 365) ASC";
}
}

This gives a nice sort by the nearest birthdays, and is simple to implement :)

Thanks a lot, boazr. This is a great, clean solution.

thanks groovekanister.
This worked nice, until some days got mis-sorted by a difference of 0-1 day, leading to the discovery that DAYOFYEAR is different every 4 years (FEB has 29 days then). So you have to change the sort critera as follows (convert the dates to "this year" to sort correctly):
$query->orderby[0]= "(
( DAYOFYEAR( DATE_ADD( node_data_field_bd.field_bd_value, INTERVAL (YEAR (NOW() ) - YEAR(node_data_field_bd.field_bd_value)) YEAR ) )
- DAYOFYEAR(NOW()) + 365 ) % 365) ASC";

A little more cumbersome on your db cpu, but works perfectly.

Oke... one more for getting only today's birthdays:

function custom_love_views_query_alter(&$view, &$query){
    if ($view->name == 'get_birthdays') {
        $where_clause="MONTH(field_birthday_value) = MONTH(curdate()) AND DAYOFMONTH(field_birthday_value) = DAYOFMONTH(curdate())";
        $view->query->add_where('birthday_filter', $where_clause);
    }
}                 

I solved this problem by using the module "views php filter" and this code:

<?php
$time = (format_date(time(), 'custom', 'm-d'));

$result = _db_query("SELECT  `nid` FROM  `content_type_bio` WHERE (DATE_FORMAT(STR_TO_DATE(field_birthday_value,  '%Y-%m-%dT%T' ) ,  '%m-%d' ) =  '". $time ."')");

while ( $obj = db_fetch_object ($result) ) { $nids[] = $obj->nid;  }
return implode(', ', $nids);
?>

Thanks for this solution!

It has a small problem, however: "views php filter" will not add any WHERE clause to the SQL query if the $nids array is empty. This means that all nodes will be selected when nobody has a birthday. A dirty hack could be to add this line before the while statement: $nids = array(-1);

thanks

I've also noticed that this solution didn't handle node revisions.
You can filter out previous revisions by joining the "{node} n" table and checking for n.vid = c.vid (c is the content_type_xxx table).

I'm using date and cck field for statistics. I have a half year, 9 months and annual statistics for the last 10 years. All these statistics are saved in nodes and cck date field - in "from date" and "to date" fields.
Now I want to select annual statistics and half year statistics in different views. Do I need filtering only by month option or is there any other possibility to do this job?

Thank you!

subscribe

Skybow's 'datesort' module posted at #13 was just what I needed - thanks so much.

I found that the 'round robin' setting of '0' did not do the job though - it only worked if there was a non zero number.

On line 71 of datesort_handler_sort.inc the if condition for whether to apply a 'round robin' is expressed like this:

if (!empty($offset)) {

The problem is that empty() returns true for a value of '0' - so to be able to use the round robin function as described I changed this line to:

if (!empty($offset) || $offset === '0') {

Now if I leave the 'round robin' value empty I get birthdays sorted from January 1 and if I set 'round robin' to '0' I get birthdays sorted from today.

+1

Skybow's module in #13 is perfect for Today in History lists. Thanks!

used #13 and it works, but it messed up all my other Views sorted normaly upon whole date :(

+1

Since it's been over three years, I think it's safe to say this issue won't get fixed in Date.

The module in #13 has been widely used, and although I had a few small problems with it (including that it clobbered the default functionality of other views that I didn't want this granularity on), it was a great inspiration for creating a new module, which I've just released: Views Date.

It has a few benefits over skybow's module in #13 (it doesn't hack the default sort criteria, but rather creates a new one; it allows you to pick more than just the month/day combination in "birthday mode"; the Date module is not a requirement) but it also has a few drawbacks (the round-robin mode isn't really working, nor does it support the CCK "Date" field type [although it does support Datetime and Datestamp]).

sbandyopadhyay: thanks a lot!

And for D7....?

For drupal 7 you need only views module for do that.

1. Add date field "field_birthday"(for example) in the user entity.

2. Add views of type "user"

3. Add contextual filter "user id" with checkbox "Allow multiple values"

4. Place this code to the "PHP contextual filter code":

$results = db_query("SELECT entity_id FROM field_data_field_birthday WHERE DATE_FORMAT(field_birthday_value,'%m-%d') = DATE_FORMAT(NOW(),'%m-%d') AND entity_type = 'user'");
$ids = array();
foreach ($results as $result) {
$ids[] = $result->entity_id;
}
return implode(',',$ids);

Demonstration: http://blondie.ru/birthdays

Check this out: try changing the Aggregation settings on your sort criteria to "Minimum" or "Maximum". For me this allowed sorting at the granularity level specified in the field.

Idea came from http://drupal.org/node/1431780

I wanted to aggregate and group together a date field (coming from a relationship, plus using Views date format SQL) at the month level and sort by month. Every time I added a sort field, my aggregation would stop grouping.

I set aggregation settings to Minimum on my sort criteria and it sorted as desired without breaking aggregation.

#55 was just what I needed! Thanks for sharing. I have been spending many hours on finding a solution for this.

#55 is working solution for D7. However, a filter on day or month only should be default behaviour of Views.

Note that for multiple values to work, the validation criteria of the contextual filter has to be enabled and at least set to 'Basis validation'.

#55 Worked for me on D7

For Drupal7,

@OFF, I did not understand "3. Add contextual filter "user id" with checkbox "Allow multiple values"" , where can I find the checkbox to "allow multiple values"?

Everything is working fine but it displays only one value not multiple. Am I missing the configuration of this checkbox "3. Add contextual filter "user id" with checkbox "Allow multiple values"" that's why it's not working?

@OFF, Now I got from where "Allow multiple values" comes and I have enabled it. But still it's not working for multiple users.

Could you please give me any hint? Any solution is welcome. :)

Sorry, I found the solution. I have enabled validation criteria to user->numeric uids. But as per #58, I have set validation criteria to Basic Validation and it works fine now. :)

So #55 works cool.

#55 is work for me. Cool.

#468456: Views filter separately for each component of the date field (YYYY) (MM) (DD) should do the trick for this one.

subscribing.

@OFF, can you please help me, where to put this code,

$results = db_query("SELECT entity_id FROM field_data_field_birthday WHERE DATE_FORMAT(field_birthday_value,'%m-%d') = DATE_FORMAT(NOW(),'%m-%d') AND entity_type = 'user'");
$ids = array();
foreach ($results as $result) {
$ids[] = $result->entity_id;
}
return implode(',',$ids);

Actually I have any Entityform, where i want a View for next week birthdays with Date and Month only excluding Year, to be displayed in view.

what do i need to do?

Thanks

@OFF, I got the PHP code option, i also pasted that code in that, but is not working,

I took
1. Name
2. Date of Birth into Fields

I followed the procedure of CONTEXTUAL FILTERS as you mentioned, what do i need to take in FILTER CRITERIA ?

Thanks