System data:
OS Ubuntu's Timezone: Asia/Tokyo(JST)
MySQL(RDS)'s Timezone: System
cron command
30 * * * * cd project && php bin/console command-name
Running the query builder from EM at the top of execute() of command:
$em = $this->getContainer()->get('doctrine')->getManager();
$newsletters = $em->getRepository('AppBundle:Newsletter')->getNewsletterForDelivery()->getQuery()->getResult();
The query builder from the command:
public function getNewsletterForDelivery()
{
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('n')
->from('AppBundle:Newsletter', 'n')
->andWhere("DATE_FORMAT(n.sendDatetime,'%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i')")
->andWhere('n.status = :status')
->andWhere('n.sendStatus = :sendStatus')
->setParameter('status', Newsletter::STATUS_DEFAULT)
->setParameter('sendStatus', Newsletter::SEND_STATUS_WAITING);
return $qb;
}
My expectations on this query builder is that it will fetch the newsletter for delivering based on the conditions above. However when cron is running the command that runs this query builder, it is not sending on the assigned time but rather after 9 hours.
The condition that has issue is the:
->andWhere("DATE_FORMAT(n.sendDatetime,'%Y-%m-%d %H:%i') <= DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i')")
Result:
TITLE Send Date Time Actual receive date time
Test 1 | 2024-11-06 16:56 | 2024-11-07 01:30
Test 2 | 2024-11-07 13:00 | 2024-11-07 22:30
test 3 | 2024-11-07 13:25 | 2024-11-07 22:30
Test 4 | 2024-11-13 09:15 | 2024-11-13 18:30
As you can see, the actual received datetime is about 9 hours after send date time even both mysql and os timezone is set to JST. And so we tried to compensated and adjusted the now to add 9 hours
Below is the code adjustment to include a +9hours time:
public function getNewsletterForDelivery()
{
// Adjust the `now` +9hours because newsletters are sent after 9hours because of system/symfony timezone issue
$adjustedNow = new \DateTime(); // Current time
$adjustedNow->modify('+9 hours'); // Add 9 hours
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('n')
->from('AppBundle:Newsletter', 'n')
// Compare sendDatetime with the adjusted current time
->andWhere("DATE_FORMAT(n.sendDatetime, '%Y-%m-%d %H:%i') <= DATE_FORMAT(:adjustedNow, '%Y-%m-%d %H:%i')")
->andWhere('n.status = :status')
->andWhere('n.sendStatus = :sendStatus')
->setParameter('adjustedNow', $adjustedNow) // Set the adjusted current time
->setParameter('status', Newsletter::STATUS_DEFAULT)
->setParameter('sendStatus', Newsletter::SEND_STATUS_WAITING);
return $qb;
}
Result:
TITLE Send Date Time Actual receive date time
Test 1 | 2024-11-21 17:00 | 2024-11-21 17:30 OK
Test 2 | 2024-11-21 17:15 | 2024-11-21 17:30 OK
Test 3 | 2024-11-21 17:30 | 2024-11-21 17:30 OK
Test 4 | 2024-11-21 17:45 | 2024-11-21 17:30 NOT OK
Test 5 | 2024-11-21 18:00 | 2024-11-21 17:30 NOT OK
Test 6 | 2024-11-21 18:15 | 2024-11-21 17:30 NOT OK
Test 7 | 2024-11-21 18:30 | 2024-11-21 17:30 NOT OK
Test 8 | 2024-11-21 18:45 | 2024-11-21 17:30 NOT OK
Test 9 | 2024-11-21 19:00 | 2024-11-21 17:30 NOT OK
And now it is sending the newsletter even if the letters are supposedly not yet to be delivered.
Note1:
Removing the DATE_FORMAT in the condition to compare the raw datetime has the same effect.
Note2: Manually executing the command from the CLI and trying to fetch one newsletter data
NOW : 2024-11-22 17:08:18 (current datetime when this was executed)
Test 1: 2024-11-21 17:00:00 (actual datetime from DB)
👆 meaning running the command manually fetches the correct newsletter
TLDR: Running manually fetches the correct newsletter (not more than senddate) while cron fetches the newsletter even if senddate is 2 hours after now
Try adding this inside the execute of command and inside query builder to force PHP/Symfony to use Asia/Tokyo timezone.
date_default_timezone_set("Asia/Tokyo");