Trying to use the month of a Datetime field in a page select statement [RESOLVED]

[Charlie W]

This is probably a simple answer, but I've been chasing my tail for a few hours trying to find the right syntax.

I'm trying to find the events that match the current month.  The event_date field is of type Datetime and I want to select those that match a particular month ($currentMonth).  I know I could select those with a date >= to the first day of a given month and <= the last day, but I'm trying to grab historical information over several years by month.

My code:

$events = $pages->find("template=event, event_date->format('m')=$currentMonth, sort=event_date");

event_date->format('m') is my failed attempt at selecting the month from a Datetime field

The error:

Unknown Selector operator: '[empty]' -- was your selector value properly escaped? field='event_date', value='->format('m')=01', selector: 'template=event, event_date->format('m')=01, sort=event_date'

Any assistance is appreciated.

[psy]

2 hours ago, Charlie W said:

$events = $pages->find("template=event, event_date->format('m')=$currentMonth, sort=event_date");

This is a database call so you can't perform operations on the field name. Maybe something like (untested):

$events = $pages->find("template=event, sort=event_date");
$eventMonth = new PageArray();
foreach($events as $event) {
	$eventDate = $event->date->format("m");
	if($eventDate === $currentMonth)
		$eventMonth->add($event);
}

 

 

[da²]

Create 2 dates, one on the first day of the month, one on the last day, and use the selector "event_date>=begin, event_date<end".

4 hours ago, psy said:

$events = $pages->find("template=event, sort=event_date");

You are potentially loading thousands of pages here.

 

Edited January 26 by da²

[Charlie W]

@psy: Thank you.  I guess I could have been more concise and just asked if I can use php functions on fields in a selector statement.  I thought there might be some built-in PW helper that I didn't know about.  I shall restructure my code.

[psy]

8 hours ago, da² said:

Create 2 dates, one on the first day of the month, one on the last day, and use the selector "event_date>=begin, event_date<end".

You are potentially loading thousands of pages here.

 

Aware of that. Wanted to keep the explanation short. Good idea to limited the event_date start and end 🙂 

[Robin S]

@Charlie W, you can use an SQL query to match a date column by part of the date, e.g. by month.

There are different ways you could incorporate this to find the pages you want, but one simple way is to first get the IDs of all matching pages, regardless of template, published status, etc, and then use those IDs as part of a PW selector.

Demo:

// First get the IDs of all pages where the month of the date value is February
$field_name = 'date'; // The name of your date field
$table_name = "field_$field_name"; // The table name is the field name prefixed with "field_"
$month_number = 2; // i.e. February, or whatever month number you want to search for
$stmt = $database->prepare("SELECT pages_id from $table_name WHERE MONTH(data) = :month_number");
$stmt->bindValue(':month_number', $month_number, \PDO::PARAM_INT);
$stmt->execute();
$ids = $stmt->fetchAll(\PDO::FETCH_COLUMN);
$ids_str = implode('|', $ids);

// Then use the IDs in a selector where you are limiting by template, applying a sort, etc.
$items = $pages->find("template=event, id=$ids_str, limit=3, sort=-date");

You can also match a date by just a day number or just a year, e.g.

WHERE DAY(data) = :day_number

WHERE YEAR(data) = :year