php - Symfony picking up wrong data based on condition in production - Stack Overflow

admin管理员组

文章数量:1122832

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)

本文标签： phpSymfony picking up wrong data based on condition in productionStack Overflow