Advsearch queryHook Question

I have an advsearch queryHook to return resources published between publishedfrom and publishedto dates.

Results should include resources published between and including these dates, but resources published on the publishedto date is not.

Here’s the hook:

<?php
$andConditions = array();

if (!empty($_REQUEST['pubfromdate']) && $pubfromdate = strtotime($_REQUEST['pubfromdate'])) {
    $andConditions['modResource.publishedon:>='] = "{$pubfromdate}:numeric";
}

if (!empty($_REQUEST['pubtodate']) && $pubtodate = strtotime($_REQUEST['pubtodate'])) {
    $andConditions['modResource.publishedon:<='] = "{$pubtodate}:numeric";
}

if (!empty($andConditions)) {
    $qhDeclaration = array(
        'qhVersion' => '1.2',
        'andConditions' => $andConditions
    );
    $hook->setQueryHook($qhDeclaration);
}

return true;

As discussed elsewhere, the advsearch documentation has not been kept up-to-date. The hook above was created some time ago, and I have no idea if it’s accurate based on the current syntax, or even if there’s a way to know this.

I wonder if there’s a timestamp included in the query that is somehow problematic?

In the queryHook you declare the version number. So I think this syntax is still ok.
(The syntax changed however for 'qhVersion' => '1.3').


You could use the &debug property in your call to AdvSearch.

[[!AdvSearch?
    ...
    &debug=`1`
]]

This outputs a lot of information to the page. Search for the SQL query SELECT DISTINCT ... and analyze it.

That’s excellent. Thanks.

The hook converts a publishto date of 12/10/2021 to ‘1639123200’, which (formatted) is Friday, December 10, 2021 8:00:00 AM.

Every record from the debug, regardless of publishto date, has the 8AM timestamp. Presumably, this is the issue.

Perhaps an offset solves this, though I’m not sure how to go about adding it.

The Unix string for the same date above but with 11:59:59PM as the timestamp is 1639785599, so the offset could / would be 662399.

This seems like the wrong timezone is used.

Maybe set the correct timezone with date_default_timezone_set in the hook before using strtotime.

What is your code to format the date/time?

Can I ask why that conclusion? If the publishto date is in the past, what result would reflect the correct timezone?

Bob, as mentioned in my comments above, I’m not sure how to address this formatting.

It’s not totally clear to me what you are doing, but if you convert a string like 12/10/2021 to a timestamp and then back to a string you should get the same result.

The following code returns 2021-12-10 00:00:00

$timestamp = strtotime('12/10/2021');
return date('Y-m-d H:i:s', $timestamp);

Only when I change the timezone between creating the timestamp and changing it back, can I see the 8 hour time offset.

The following code returns 2021-12-10 08:00:00

date_default_timezone_set("America/Los_Angeles");
$timestamp = strtotime('12/10/2021');

date_default_timezone_set("Europe/London");
return date('Y-m-d H:i:s', $timestamp);

With debug active, the datetime value returned for 12/10/2021 is 1639123200. This instance of ModX uses the America/Los_Angeles timezone, so I guess that’s where this occurs. Do you have a suggestion for resolving this?

I’m still not sure what exactly the problem is.

1639123200 is the correct value for your timezone.
If you run this snippet

<?php
return date('Y-m-d H:i:s', 1639123200);

it should return the value 2021-12-10 00:00:00.


My main point is, just make sure that you use the same timezone to enter your data as you use to query it. Then there shouldn’t be any problem.

Nor am I.

<?php
return date('Y-m-d H:i:s', 1639123200);

returns 2021-12-10 00:00:00 from 1639123200.

Thanks for the feedback. I don’t know where the disconnect is between the entered and queried timezone.

Is there even a disconnect?
When you open your resources in the manager and check the date in the field “Published On”. Is that date correct?

The date is correct.