selector questions about pages with datetime field

[PeterDK]

So assuming I have pages with a datetimefield, how can i do the following?

Select pages from a year.

// find all pages with events in 2014

$results = $pages->find("events.date>=2014-01-01, events.date<2014-12-01");

Is this correct?

Select pages that were on a saturday.

I have no clue, where can i find some documentation that covers this

[Craig]

Select pages from a year.

// find all pages with events in 2014

$results = $pages->find("events.date>=2014-01-01, events.date<2014-12-01");

Is this correct?

Almost! You would just need to alter the end date selector to be like this:

events.date<=2014-12-31
 

Select pages that were on a saturday.

I have no clue, where can i find some documentation that covers this

I'm not 100% sure about this, but you may have to extend the datetime inputfield to get this functionality. Perhaps you could do this by adding a new DB column for day_number/day_name, which is calculated and updated on save, to allow you to query on it. You can select by days using native MySQL date-related functions, and this would probably involve customising the getMatchQuery function of the inputfield.

Ryan might know better about this one

[Martijn Geerts]

Google this:

site:processwire.com/talk date selector

[dragan]

Perhaps not the most elegant solution, but this seems to work:

(make sure to choose "none" from the formatting options in your date-field)

$d = $pages->find("template='basic-page',datum>0");

foreach($d as $dat) {
	$wd = date('w', $dat->datum); // weekday, 0 - 6 = Sunday to Saturday
	if($wd == 6) {
	    $output .= "<p>Unformatted date: {$dat->datum} / Page: <a href='{$dat->url}'>{$dat->title}</a></p>";
	} 
}

[adrian]

Select pages that were on a saturday.

This is one of those cases where an SQL query would be easier.

WHERE DAYOFWEEK(date) = '6'

But it probably isn't worth it to do an SQL query for such a simple situation, so you might be best just removing the non-Saturday dates from the results.

$results = $pages->find("date>=2014-01-01, date<=2014-12-31");
foreach ($results as $result) {
    if (date('l', $result->date) != 'Saturday'){ 
        $results->remove($result);
    }
}
 

[SiNNuT]

Or you could lookup all the Saturdays in a given time-frame and build a selector from this. I don't know the inner workings of the selector engine and the queries it produces so maybe not efficient at all, but i've tested it and it seems to work.

$start = new DateTime('2013-12-31');
$end = new DateTime('2015-01-01');

// grab all Saturdays between $start and $end
$periodInterval = DateInterval::createFromDateString('first sat');
$periodIterator = new DatePeriod($start, $periodInterval, $end, DatePeriod::EXCLUDE_START_DATE);

// build selectorValues string
$selectorValues = '';

foreach ($periodIterator as $date) {
    $selectorValues .= $date->format('Y-m-d') . '|';
}

$selectorValues = rtrim($selectorValues,'|'); // 2014-01-04|2014-01-11|2014-01-18|2014-01-25 etc.

$results = $pages->find("test_date=$selectorValues");

foreach ($results as $result) {
    echo $result->title;
}