Doctrine Utc Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require brandonlinu/doctrine-utc-bundle
   

   Add to config/bundles.php:

   return [
       // ...
       BrandonlinU\DoctrineUtcBundle\DoctrineUtcBundle::class => ['all' => true],
   ];
   

2. Configuration Override default UTC behavior in config/packages/doctrine_utc.yaml:

   doctrine_utc:
       default_timezone: 'UTC'  # or 'Europe/Paris' if needed
       use_utc_for_all: true   # forces UTC for all datetime fields
   

3. First Use Case Query a DateTime field in UTC:

   $user = $entityManager->getRepository(User::class)->find(1);
   $createdAtUtc = $user->getCreatedAt()->format('Y-m-d H:i:s\Z'); // Always UTC
   

Implementation Patterns

Core Workflows

1. UTC-Only Entities Annotate entities to enforce UTC:

   use BrandonlinU\DoctrineUtcBundle\Annotation\UTC;
   
   /**
    * @UTC
    */
   class User {
       /**
        * @ORM\Column(type="datetime")
        * @UTC
        */
       private $createdAt;
   }
   

2. Timezone-Aware Queries Convert query results to a specific timezone:

   $queryBuilder->addSelect('UTC_CONVERT(u.createdAt, :timezone) as createdAtLocal')
                ->setParameter('timezone', 'America/New_York');
   

3. Form Handling Use UTCType in Symfony Forms:

   $builder->add('createdAt', UTCType::class, [
       'widget' => 'single_text',
       'html5' => true,
       'attr' => ['placeholder' => 'YYYY-MM-DD HH:MM:SS (UTC)'],
   ]);
   

Integration Tips

• Doctrine DQL: Use UTC_NOW() for UTC timestamps:

  $qb->where('u.createdAt > UTC_NOW() - INTERVAL 1 DAY');
  

• API Responses: Normalize all datetime outputs to UTC in serializers:

  use JMS\Serializer\Annotation\Type;
  use JMS\Serializer\Annotation\SerializedName;
  
  /**
   * @Type("DateTime<'Y-m-d\TH:i:s\Z'>")
   * @SerializedName("created_at")
   */
  public $createdAt;
  

• Migrations: Ensure UTC defaults in schema updates:

  $this->addSql('ALTER TABLE user ADD created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP');
  

Gotchas and Tips

Pitfalls

1. Legacy Data Issues

   • Pre-existing non-UTC timestamps may cause inconsistencies. Use:

     $entityManager->getConnection()->getDatabasePlatform()->convertToDatabaseValue(
         $legacyDate,
         DoctrineUtcBundle::UTC_TYPE
     );
     

   • Fix: Run a one-time migration to normalize timestamps.

2. Timezone Ambiguity in Queries

   • Avoid mixing UTC_CONVERT with raw DateTime objects in the same query. Example of wrong:

     // ❌ Ambiguous: Mixes UTC and local time
     $qb->where('u.createdAt > :now AND UTC_CONVERT(u.createdAt, :tz) > :nowLocal');
     

   • Fix: Stick to one timezone per query or use subqueries.

3. Caching Quirks

   • Doctrine’s second-level cache may serialize datetimes incorrectly. Disable for UTC entities:

     # config/packages/doctrine.yaml
     doctrine:
         orm:
             entity_managers:
                 default:
                     metadata_cache_driver: array
                     query_cache_driver: array
                     result_cache_driver: array
     

Debugging

• Check UTC Conversion:

  $utcTime = $entity->getCreatedAt();
  $localTime = $utcTime->setTimezone(new \DateTimeZone('Europe/Paris'));
  dump($utcTime->format('c'), $localTime->format('c')); // Compare outputs
  

• Enable SQL Logging:

  # config/packages/dev/doctrine.yaml
  doctrine:
      dbal:
          logging: true
          profiling: true
  

  Look for UTC_CONVERT or UTC_TIMESTAMP in logs.

Extension Points

1. Custom Type Handling Extend the bundle’s UTCType for custom fields:

   use BrandonlinU\DoctrineUtcBundle\Type\UTCType;
   
   class CustomUTCType extends UTCType {
       public function convertToDatabaseValue($value, AbstractPlatform $platform) {
           if ($value instanceof \DateTimeInterface) {
               return $value->setTimezone(new \DateTimeZone('UTC'))->format('Y-m-d H:i:s');
           }
           return parent::convertToDatabaseValue($value, $platform);
       }
   }
   

   Register in doctrine_utc.yaml:

   doctrine_utc:
       custom_types:
           custom_utc: BrandonlinU\DoctrineUtcBundle\Type\CustomUTCType
   

2. Event Listeners Hook into UTC conversion events:

   use BrandonlinU\DoctrineUtcBundle\Event\UTCConvertEvent;
   
   $eventDispatcher->addListener(
       UTCConvertEvent::PRE_CONVERT,
       function (UTCConvertEvent $event) {
           if ($event->getTimezone() === 'UTC') {
               $event->setTimezone('America/Los_Angeles'); // Override
           }
       }
   );
   

3. Database-Specific Optimizations

   • PostgreSQL: Use AT TIME ZONE for better performance:

     $qb->where('u.createdAt AT TIME ZONE :timezone > :cutoff')
       ->setParameter('timezone', 'UTC')
       ->setParameter('cutoff', '2023-01-01 00:00:00');
     

   • MySQL: Avoid CONVERT_TZ in queries; pre-convert in PHP when possible.