Time string in selector

[ttttim]

I'm building a filter system for courses where users can select a start and end time and I want to get all the courses within the selected time window. In the backend I have two datetime fields that holds the time. The filtered time is a string like '11:15'. To what do I convert the string to be used in a selector:

start_time>=$startTimeString, start_time<=$endTimeString, end_time>=$startTimeString, end_time<=$endTimeString

And do I need to set the Time Output Format?

[BillH]

I think the information you need is in this post:

So, in brief, the values in a selector should be UNIX timestamps.

Note that, in a template, if you get a date from a field it will be formatted according to the settings for the field, but you can use $page->getUnformatted('your_date_field') if you need the timestamp.

 

 

[ttttim]

Hi @BillH

Thanks for your reply. In the post you've mentioned the time is not relevant where in my case the date isn't relevant just the time. I believe that in php time is always connected to a date that's why i'm not sure how to use the selector just for time. 

My alternative is to create an extra field in the back-end which will hold a string like '930' for 9:30 etc. But this seems like an ugly work around. 

[MoritzLost]

Disclaimer: Correct me if I'm wrong!

I don't think what you're trying to do is possible using ProcessWire's datetime fields. The problem is that datetime fields always use MySQLs datetime column type, which represents a fixed date and time, never only a time of day. So even if you're outputting only the time, if the start & end time fields are datetime fields, they're gonna be stored as datetime data. This is why a smaller / greater than selector can't work for a "time of day" scenario, because you can't really compare a timestamp with a time of day.

Note that if you want to go one level deeper and go with direct database queries, you can extract the time portion of the datetime field using something like the TIME function and then do the comparison with that. Might be slow though, unless you add an index for the TIME values. Or maybe that can be done with some of the advanced finder module (RockFinder3 maybe?).

A ProcessWire native solution would be to add a hidden field that acts sort of like an index for the time, and use that for filtering instead. I've described this approach here. For your use-case, you could use two hidden integer fields for the start and end time. Then use a hook to update them automatically with the start and end time in the form of seconds from midnight. Then use those fields to filter by in your selector.

[dragan]

34 minutes ago, MoritzLost said:

A ProcessWire native solution would be to add a hidden field that acts sort of like an index for the time, and use that for filtering instead. I've described this approach here. For your use-case, you could use two hidden integer fields for the start and end time. Then use a hook to update them automatically with the start and end time in the form of seconds from midnight. Then use those fields to filter by in your selector.

That's what I was going to suggest as well. A little hint:

[ttttim]

Many thanks, it was the alternative I thought of but I just assumed I missed something else with the selector.

It’s nice to see that it wasn’t such an ‘ugly’ idea as I thought it was

[kongondo]

Maybe I misunderstood the OP but isn't the trick to always use the same date (portion) in your datetime field, just with different times? Initially, I was going to suggest that you use a hook to change the date portion of your datetime field (it's just a string) to an arbitrary fixed date (even epoch if you want) before save, until I noticed ProcessWire does the same. If date isn't relevant, then you can use the new HTML5 datetime input and only use the time portion. You will notice if you look at the database that ProcessWire will save the time to a fixed date (2010-04-08) and your selected time. It's the same date used for WireCache expireNever.

[kongondo]

If what I said above describes your situation, I have tested and the following works. I used the HTML5 datetime picker, TIME only. ProcessWire will default to a date of 2010-04-08 as mentioned in my previous post, so we use that always, just with different times.

$startTimeString = "2010-04-08 09:15:00";
$endTimeString = "2010-04-08 11:00:00";
$courses = $pages->find("template=course,start_time>=$startTimeString, start_time<=$endTimeString, end_time>=$startTimeString, end_time<=$endTimeString");
bd($courses,'COURSES');

 

 

[BillH]

Hadn't read your original post properly ?

Just another thought: do you need to use datetime fields?

If there are a fairly limited number of possible start and end times during the day, you could set up pages for each time, and select using page reference fields. The time pages could have fields with formatted and numerical representations of the time.

To find pages in a time range, you'd find the relevant time pages, and then use those to find the courses, something along the lines of this:

$startTimes = $pages->find("template=times, time>=1200, time<=1400");
$endTimes = $pages->find("template=times, time>=1500, time<=1700");
$courses = $pages->find("template=course, start_time=$startTimes, end_time=$endTimes");

Not tested, but I think it'd work.

[LostKobrakai]

4 hours ago, kongondo said:

If what I said above describes your situation, I have tested and the following works. I used the HTML5 datetime picker, TIME only. ProcessWire will default to a date of 2010-04-08 as mentioned in my previous post, so we use that always, just with different times.

Is this documented somewhere? Feels like a gross implementation detail bound to change at any given point in time.

Using this would imo be the proper solution:

 

[ttttim]

Thanks again for all the replies! The easiest solution for me was to add two hidden fields containing an integer of the time 900,1215,etc.

@LostKobrakai I'm definitely going to look into this for future update.

[kongondo]

40 minutes ago, LostKobrakai said:

Is this documented somewhere? Feels like a gross implementation detail bound to change at any given point in time.

Its in the source code and I doubt it will change (like Admin ID = 2). It's used internally by ProcessWire, hence I doubt if it will ever be documented.  For backward compatibility, I don't think Ryan will ever change it to something else. Either way, one never knows, and like you say, it might change, though that is highly unlikely. However, a safer option is to use one's own arbitrary fixed date and a hook, e.g. 1970-01-01, or whatever. 

[LostKobrakai]

18 minutes ago, kongondo said:

However, a safer option is to use one's own arbitrary fixed date and a hook, e.g. 1970-01-01, or whatever.

Or a time column instead of a datetime one, removing the complexity all together.

[kongondo]

9 minutes ago, LostKobrakai said:

Or a time column instead of a datetime one, removing the complexity all together.

True