Prefilter by day of week

[BasilC]

Is it possible to prefilter a table by the day of the week. For example, have a list that only includes events that occur on a Sunday?

 

[genyded]

Assuming you are using mysql, 1 = Sunday, 2 = Monday, ?, 7 = Saturday...

... WHERE DAYOFWEEK(your_table___element_name) = 5

for Thursday or whatever number for the day you want based on the above.

Dale

 

[BasilC]

Thanks, genyded. But I don't know how to incorporate that. I already have two conditions set up - one specifying a date range, the other filtering by a substring in one of the fields. Those were easy to do. But I don't know where to add in your query or what goes before ...WHERE. Sorry to be so stupid...

 

[genyded]

No worries - this is a bit trickier than I first suspected... but here is how you can do it...

1) Add a new CALC element/field to the list/table and in the calculation put something like...

return  date('w', strtotime(ep_conferences___date_time));

l

... only replace the element I have there with yours. That should give you a list of the days of the week (numeric as previously indicated) for the existing data field (which you an make hidden if you do not want it showing in the list/form). Then you can just add a list pre-filter where the new filed is = to the number for the day(s) that you want...

Does that all make sense?

 

[BasilC]

Thanks. That makes sense, but for some reason it calculates every date as being a Thursday - "4". I tried changing 'w' to 'l' to get the weekday name, but it still shows 'Thursday".

 

[BasilC]

Got it!

$phpdate = strtotime( '{broomfieldevents___eventdate}' );
$dayofweek = date('l',$phpdate);
return $dayofweek;

The trick was to first convert the date from mySQL to PHP format. I have dates stored as YYYY-MM-DD, PHP stores dates as the number of seconds since the Beatles' first LP or something along those lines.
Thanks for your help - even though your suggestion didn't work for me, it put me on the right track when I otherwise wouldn't have had a clue. Are your dates already stored in PHP format?

 

[genyded]

Yes - I forgot about the conversion - sorry. Glad you were able to figure it out!

Dale