Because 0000-00-00 00:00:00 will always be less than the current datetime stamp, I am understanding your requirements as:
published = 1
publish_up <= now
publish_down = 0000-00-00 00:00:00
publish_down > now
where() has a default glue value of AND.
andWhere() has a default glue value of OR.
To translate that to Joomla's ...
As demonstrate by my answer at with insert on duplicate key update - updating multiple rows at once, I recommend using Joomla's secure query building methods, then performing any necessary surgery when calling setQuery().
I reckon this is the best you can do. Just be sure that your surgery CANNOT accidentally "over-replace" or otherwise ...
After more hours trawling through the Joomla libraries to understand how the Query process is working, I can say that converting the $query object to a SQL command string and replacing the current statement with the required statement is the easiest way that I can see to make this work. This is what is covered in the linked answer by @mickmackusa.
I have reverse engineered a minimal verifiable example on db-fiddle.com based on your question and answer details.
Using my reduced and generic sample data with the sql in your answer, the output is: (DB Fiddle Demo)
I think the simplest way forward is to use a JOIN to relate your first query to your second query. An INNER JOIN (same as JOIN) will only generate rows in the result set where both "derived" queries have data.
I am assuming that %%P1=0%% is your Plotalot placeholder.
This was a late-night endeavour, so I cannot guarantee that it is fully refined, ...