Find upcoming birthdays

[MichaMichaMicha]

I have stored a lot of users with their birthdate. Now I'm trying to find the birthdays for the next 7 days but without succes.

Already tried things like:

template=user,birthdate$=02-26 but without success.

It can be done with a raw MySQL-query, but I would like to prevent anything like that.
One thing that I've thought of was imploding the current day for the last 100 years, (birthdate=2016-09-26|2015-09-26|2014-09-26,etc) but that would result in a 1100 chars long selector....  

 

Anyone has an idea?

[LostKobrakai]

That's not possible with the processwire selector engine, as datefields are stored as timestamps and there's no abstraction around the mysql date functions which would allow comparing only day and month.

[Robin S]

I think this should work:

$today = strtotime('today');
$date_end = strtotime('+7 days', $today);
$pages->find("template=user, birthdate>=$today, birthdate<$date_end");

Ha, of course that won't work because you have the birth date, which could be from any year.

 

New idea...

I think in order to find birthdays in a $pages->find() selector you'll need to store the birthdate day of the year in a separate field in your user template. You could create an integer field "day_of_year" and then populate it from the birthdate field with a save hook:

$this->pages->addHookBefore('saveReady', function($event) {
	$page = $event->arguments('page');
	if($page->template != 'user') return;
	if($page->birthdate) {
		$page->day_of_year = date('z', $page->birthdate);
	}
});

And to find birthdays:

$day = date('z');
$day_end = date('z', strtotime('+7 days'));
if($day_end < 7) {
	$results = $pages->find("template=user, (day_of_year>=$day), (day_of_year<$day_end)");
} else {
	$results = $pages->find("template=user, day_of_year>=$day, day_of_year<$day_end");
}

 

[BitPoet]

As I've been pondering that same task a few times before, I've drawn up a quick&dirty proof-of-concept module that extends PW's regular FieldtypeDatetime, adding (database) search functionality by using date_format. It's lacking sanitizing and not really thought through all the way, but I'd love to get some thoughts on whether this might be worthwhile to pursue.

<?php

/**
 * Advanced Datetime Field
 *
 * Allows searching for datetime fields by passing a format string
 * to MySQL's format_date function as a subfield (only one format option
 * and without the % sign).
 *
 * A list of all format strings can be found at
 * http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format
 *
 * Examples:
 * $pages->find("my_date_field.e=27")
 * // Searches for all pages having the 27th in my_date_field
 *
 * $pages->find("my_date_field.Y=2016")
 * // Searches for pages with year 2016 in my_date_field
 *
 * $start = date('z') + 1; // PHP starts at 0, MySQL at 1
 * $end = $start + 6;
 * $pages->find("my_date_field.j>=$start, my_date_field.j<$end")
 * // All pages in the seven day period starting today
 **/

class FieldtypeDatetimeAdvanced extends FieldtypeDatetime implements Module {
	public static function getModuleInfo() {
		return array(
			"title"			=>	"Advanced Datetime field",
			"summary"		=>	"Datetime field with extended search syntax",
			"version"		=>	"0.0.1",
		);
	}

	/**
	 * Match a date/time value in the database, as used by PageFinder
	 *
	 */
	public function getMatchQuery($query, $table, $subfield, $operator, $value) {
		
		if($subfield != "data") {
			$value = $this->_sanitizeValue($value);
			$database = $this->wire("database");
			if($database->isOperator($operator)) {
				$table = $database->escapeTable($table);
				$value = $database->escapeStr($value);
				$query->where("DATE_FORMAT({$table}.data, '%{$subfield}'){$operator}'$value'");
				$this->log->message($query->getQuery());
			}
		} else {
			$this->log->message("Regular query");
			$value = (int) $this->_sanitizeValue($value); 
			if($value) $value = date('Y-m-d H:i:s', $value); 
				else $value = '';
			$database = $this->wire('database');
			if($database->isOperator($operator)) {
				$table = $database->escapeTable($table);
				$subfield = $database->escapeCol($subfield);
				$value = $database->escapeStr($value);
				$query->where("$table.{$subfield}{$operator}'$value'");
			}
		}
		return $query; 
	}
}

 

[adrian]

I would absolutely love this - thought about it a few times, but never got started on it.

This might be too much / too hard, but I'd actually love if it was more than just date/time, but actually allowed us to use all MySQL operators in our selectors. But I haven't really thought through what would be involved in making this possible.

[BitPoet]

4 hours ago, adrian said:

I would absolutely love this - thought about it a few times, but never got started on it.

This might be too much / too hard, but I'd actually love if it was more than just date/time, but actually allowed us to use all MySQL operators in our selectors. But I haven't really thought through what would be involved in making this possible.

Calling arbitrary MySQL functions would be nice, but I think these date features should probably be a different topic, as I'd prefer it if they also worked for filtering PageArrays. That would AFAIK mean using a wrapper class in place of the plain timestamp that is currently used as the unformatted value though. Got to fiddle with that when I have time in the evening.

[BitPoet]

Okay, here we go. FieldtypeDatetimeAdvanced with database and PageArray filtering support and descriptive naming.

Possible subfields:

• day
• month
• year (4-digit)
• hour (0..23)
• minutes
• seconds
• day_of_week (0..6, 0 = Sunday)
• day_of_year (0..365)
• week_of_year (1..53)

Examples:

// Database search:
$pagelist = $pages->find("mydatefield.year=2016");

// Filtering PageArray in memory:
$maypages = $pagelist->filter("mydatefield.month=5");

// Back to our starting point:
$start = date('z');
$end = $start + 7;
$sevendays = $pages->find("mydatefield.day_of_year>=$start, mydatefield.day_of_year<$end");

 

[LostKobrakai]

I'd really like to see that in the core fieldtype.

[Robin S]

6 hours ago, BitPoet said:

Okay, here we go. FieldtypeDatetimeAdvanced with database and PageArray filtering support and descriptive naming.

Really stoked about the new module!

For the OP's issue, the below won't work as expected across the year boundary. Like, if this runs on 28th of December.

6 hours ago, BitPoet said:

 

// Back to our starting point:
$start = date('z');
$end = $start + 7;
$sevendays = $pages->find("mydatefield.day_of_year>=$start, mydatefield.day_of_year<$end");

 

Hence the need for switching between two find queries...

On 27/09/2016 at 9:39 AM, Robin S said:

 

$day = date('z');
$day_end = date('z', strtotime('+7 days'));
if($day_end < 7) {
	$results = $pages->find("template=user, (day_of_year>=$day), (day_of_year<$day_end)");
} else {
	$results = $pages->find("template=user, day_of_year>=$day, day_of_year<$day_end");
}