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 18 hours ago 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 🙂