Relative time used in selectors

[Robin S]

It's cool to be able to use relative times in selectors like:

post_date>='90 minutes ago'

But for debugging purposes I'd like to be able to get the timestamp that '90 minutes ago' actually results in. How can I do that?

I thought it would be the same as PHP's strtotime, but in a selector:

post_date>='90 minutes ago'

Produces different results to:

$mytime = strtotime('90 minutes ago');
...
post_date>=$mytime

[gebeer]

I think, PW is not just using strtotime() to convert the relative date to a timestring. You can take a look at wire/core/WireDateTime.php, to see how strings are converted to timestamps.

It is also worth looking into the $datetime class and see how you could utilize that.

But, this doesn't really answer your question why it is that you are getting different results. I would also expect to get the same results. Only thing I can think of is time zone conflict. Maybe your strtotime() operation is using a different time zone than the internal PW calculations? But this is just a wild guess.

To see if this is the case, you could try and use date_default_timezone_set() before using strtotime() and getting the pages.

[Robin S]

Thanks @gebeer, I will definitely check out those parts of the PW code.

I need to do more testing and will post back when I do, but based on some quick tests it seemed that the strtotime way was producing correct results while the native PW relative date was producing incorrect results. I do have the right timezone set in my config though.

[LostKobrakai]

Just keep in mind that $pages->find() can use those date strings, whereas further in memory filtering of pages can only handle raw timestamps.

[Robin S]

Thanks @LostKobrakai, I think you may have hit upon what is giving me the unexpected results when using a relative time in a selector.

I did some testing with a $pages->find() selector on a datetime field and the results were identical for strtotime and for having the relative time in the selector. So no problem there.

But the selector that is giving me odd results when the relative time is in the selector vs stored in a variable as a timestamp with strtotime is:

$comment_page->comments->find("created_users_id=$this_user, created>='20 hours ago'")

This uses find on a comments field - does this find operation filter in memory or with a database query? Is there a way to know when a particular find operation will be performed in memory?

[BitPoet]

But the selector that is giving me odd results when the relative time is in the selector vs stored in a variable as a timestamp with strtotime is:

$comment_page->comments->find("created_users_id=$this_user, created>='20 hours ago'")

This uses find on a comments field - does this find operation filter in memory or with a database query? Is there a way to know when a particular find operation will be performed in memory?

It's not just about finding in memory vs. finding in the database. Generally, you can only assume that relative timestamps work when you're callling find() on $pages/wire('pages').

In your example, you're calling find() on the comments fieldtype. You're not looking for pages (comments aren't pages but rather entries in a multivalued field, like an extended FieldtypeTextarea). FieldtypeComments::find casts a good number of selector values to integer when assembling the database query:

if(in_array($f, array('id', 'status', 'created', 'pages_id', 'parent_id', 'created_users_id', 'upvotes', 'downvotes'))) {
	$_sql .= "AND $f$operator" . ((int) $value)  . " ";

Thus, you can assume that relative timestemps work when there's a $pages or wire('pages') (or $this->pages inside a module) in front of your find() call.

It probably won't work if you are calling $somepage->somefield->find(), unless somefield is a page field.

It won't work if you call it on a PageArray (or other WireArray), so $pages->find(someselector)->find(selector_with_relative_timestamp) will fail.

Relative timestamps are hardcoded in core/PageFinder.php to work for "created", "modified" and "published".

It would probably be feasible to modify FieldtypeComments::find to employ the same logic:

        if(in_array($f, array('id', 'status', 'pages_id', 'parent_id', 'created_users_id', 'upvotes', 'downvotes'))) {
                $_sql .= "AND $f$operator" . ((int) $value)  . " ";

            } else if($f == 'created') {
                $value = (ctype_digit($value)) ? (int)$value : strtotime($value);
                $value = date('Y-m-d H:i:s', $value);
                $_sql .= "AND $f$operator'" . $value . "' ";

I've taken the liberty to file a feature request on github.

[Robin S]

Thanks @BitPoet, you've helped me understand this better. It was because my relative time string was cast to an integer that I was getting confusing results (rather than an error or no results at all).

Although in regards to...

It won't work if you call it on a PageArray (or other WireArray), so $pages->find(someselector)->find(selector_with_relative_timestamp) will fail.

Relative timestamps are hardcoded in core/PageFinder.php to work for "created", "modified" and "published".

...I ran a couple of tests and it seems that relative times work for any PageArray - so they do work for  $pages->find(someselector)->find(selector_with_relative_timestamp).  Edit: does not work

And relative times seem to work for any custom Datetime field, so not just "created", "modified" and "published".

But you've nailed it about the Comments fieldtype, which of course is not a PageArray. Would be nice to use relative times with a Comments find so thanks for making that Github request. Although now I understand a bit more about when relative times are possible and when they're not it's pretty easy to just do:

$time = strtotime('2 days ago')

or whatever and use the variable in the selector.

And a little off-topic, but for anyone wanting to do a find on comments across multiple pages this function from Ryan's blog profile is very useful.

FieldtypeComments::findComments should probably be mentioned in the Comments docs.

[BitPoet]

I'm still a bit stumped to read that find() on PageArrays works for you. I just tested again on 2.7 and 3.0.16, and in both versions, I don't get any results if try to filter a PageArray by a relative time. To illustrate that:

$old = $pages->find("created>='65 days ago'");
// this gives back 8 results

$fresh = $pages->find("created>='30 days ago'");
// this gives back 1 result

$filtered = $old->find("created>='30 days ago'");
// this gives back an empty PageArray, though getTotal() still returns 8 ()

[LostKobrakai]

getTotal is not reevaluated as it's meant to be used for pagination and only the db can know the unlimited number of pages for a certain selector. That's why you need a single selector to select pages if you need to work with pagination.

[Robin S]

I'm still a bit stumped to read that find() on PageArrays works for you. I just tested again on 2.7 and 3.0.16, and in both versions, I don't get any results if try to filter a PageArray by a relative time.

Sorry, sloppy testing on my part. You're absolutely right; relative time only works for $pages->find(). Shame though, as would be nice to use it for PageArrays.