How are timestamps set for scheduled jobs
@MikeyMJCO suggested I post this question after I asked it in mattermost#dev https://chat.civicrm.org/civicrm/pl/fm9tpndnuprnjqb8szzo4adtmh
I was surprised to see the status message: Cron is not running... last cron-run (4 hours in the future!)
Summary of my understanding (at the end of a day, with an addled brain from troubleshooting a failed mailing):
The civicrm_job_log table is the repo for the scheduled jobs log. It has TIMESTAMP fields which are dark magic for storing date-time values.
From the mysql docs
When you retrieve a TIMESTAMP value that was inserted by a client in a different time zone, you will get a value that is not the same as the value stored in the database. As long as you don’t change the time zone, you can get the same TIMESTAMP value that you stored.
... meaning, in addition to a universal time for the timestamp storage... the timezone of the client connection that created the record is encoded in the timestamp.
I am still hazey on what happens when you retrieve a timestamp value. In my case, I was seeing mixed timezone results until I set the timezone of my mysql connection with SET time_zone='+00:00';
This caused me to get timestamps that fit with my expectations. The reason for the discrepancy is that some jobs had been created when scheduled jobs were triggered from within the UI... thereby having the mysql connection set to the CMS's configuration. The alternate timezone was set when scheduled jobs were run using civicrm/bin/cli.php.
This should not happen anymore as this PR https://github.com/civicrm/civicrm-core/pull/12609 ... ensures that bin/cli.php bootstrapping sets the timezone consistently.
However, it is still pertinent to know that if you are working with the CiviCRM database via a direct MySQL connection, timestamps will be impacted.