Improved support for local MySQL time about cavalcade HOT 8 CLOSED

Is your database server's time offset 5 hours, or is the timezone itself just set to UTC-5? From what I can see, datetime columns (as used in Cavalcade) are always stored as UTC, so it's only when we cast it in order to compare to now() that it's an issue.

What I think you might be able to do is use per-connection timezones in both WordPress and Cavalcade-Runner to enforce the use of UTC. For Cavalcade, you should be able to do that with:

HM\Cavalcade\Runner::instance()->hooks->register( 'Runner.connect_to_db.connected', function ( $db ) {
	$db->exec( 'SET time_zone = "+00:00"' );
} );

(You may want to do this on the WordPress side; the code for that is left as an exercise for the reader.)

Does that solve the issue for you? We could potentially look at building this in to Cavalcade, or changing NOW() to use date( 'Y-m-d H:i:s' ) (although I'm unsure if the latter would affect query caching).

from cavalcade.

I did some local testing.

• I ran SELECT NOW() which returned 2018-03-04 03:34:00 (the UTC time).
• I then ran INSERT INTO wp_cavalcade_jobs SET site=1, hook="fake", args="a:0", start="2018-03-04 13:34:00", nextrun="2018-03-04 13:34:00"
• I ran SELECT * FROM wp_cavalcade_jobs WHERE start < NOW() and got 0 results
• I ran SET time_zone = "+10:00"
• I ran SELECT NOW() and got 2018-03-04 13:34:00 (-ish)
• I ran SELECT * FROM wp_cavalcade_jobs WHERE start < NOW() and got 1 result

So it appears that indeed the time_zone variable does affect NOW() but does not affect the datetime column, so setting the time_zone variable on connect should work correctly here.

from cavalcade.

@rmccue Thanks for this - you're right that time_zone is the culprit. From what you've suggested here (which aligns with my tests), setting time_zone in the runner seems to be the crux of the issue.

from cavalcade.

I've just run into this issue as well.

The proposed fix, adding:

HM\Cavalcade\Runner::instance()->hooks->register( 'Runner.connect_to_db.connected', function ( $db ) {
	$db->exec( 'SET time_zone = "+00:00"' );
} );

didn't work for me. Adding:

$this->db->exec( 'SET time_zone = "+00:00"' );

after we create the PDO instance works though.

from cavalcade.

@discoinfiltrator These should be equivalent, so not sure what's happening there. You may need to check that your callback is actually being executed; might be that where you have that code is not being run?

from cavalcade.

Ah, my bad. I had added the code snippet incorrectly. Both are solving the problem for me.

from cavalcade.

Fantastic to hear.

I'd be happy to add this to the Cavalcade Runner code itself since it simply makes things consistent with no downsides for others, if anyone wants to open a PR :)

from cavalcade.

Sounds great. I've created: humanmade/Cavalcade-Runner#44

from cavalcade.