Getting Started

Minimal Steps

Installation:
```
composer require assistenzde/database-timezone
```
The package auto-registers via Symfony’s autoloader.
Configure: Create /config/packages/database_timezone.yaml with your desired timezone (e.g., UTC):
```
database_timezone:
  database: UTC
```
First Use Case:
- Model Fields: All DateTime, DateTimeImmutable, and Date fields in Doctrine entities will now automatically convert to the configured timezone (e.g., UTC) when saved to the database.
- Query Results: When retrieving data, the package ensures consistency by converting stored values back to the application’s timezone (or the configured one) for PHP-level operations.

Implementation Patterns

Core Workflow

Entity Definition: Define your Doctrine entities as usual. The package handles timezone conversion transparently for fields like:

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class Event
{
    #[ORM\Column(type: 'datetime')]
    private DateTimeInterface $scheduledAt;

    // Getters/setters...
}

Database Operations:
- Insert/Update: The package converts DateTime values to the configured timezone (e.g., UTC) before persisting to the database.
- Select: Fetched values are converted back to the application’s timezone (or the configured one) for consistency.

QueryBuilder Integration: Use the package’s DateTime handling in queries:

$events = $entityManager->createQueryBuilder()
    ->select('e')
    ->from(Event::class, 'e')
    ->where('e.scheduledAt > :now')
    ->setParameter('now', new DateTime('now', new DateTimeZone('UTC')))
    ->getQuery()
    ->getResult();

Raw SQL Queries: For raw queries (e.g., via DBAL), manually convert values:

$conn->insert('events', [
    'scheduled_at' => (new DateTime('now', new DateTimeZone('UTC')))->format('Y-m-d H:i:s'),
]);

Advanced Patterns

Custom Timezone per Entity: Override the global config for specific entities using annotations or listeners (requires extension; see Gotchas).

Timezone-Aware Validation: Combine with Symfony’s DateTimeType or custom validators to enforce timezone rules:

#[Assert\Type(type: DateTimeInterface::class)]
#[Assert\Expression(
    expression: "value.getTimezone() === new DateTimeZone('UTC')",
    message: "Timezone must be UTC."
)]
private DateTimeInterface $scheduledAt;

Migrations: When writing migrations, ensure timezone consistency:

$this->addSql('ALTER TABLE events MODIFY scheduled_at DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP())');

Gotchas and Tips

Pitfalls

Global vs. Per-Entity Timezones: The package enforces a single timezone for all entities via config. To support multiple timezones:
- Extend the package by overriding the DatabaseTimezoneListener or using a custom event subscriber.
- Example: Add a timezone property to entities and handle conversion in a lifecycle callback.

Raw Queries and DBAL: The package does not auto-convert values in raw SQL or DBAL queries. Always convert manually:

// ❌ Avoid (no conversion)
$conn->executeStatement('INSERT INTO events (scheduled_at) VALUES (?)', [new DateTime()]);

// ✅ Correct (manual conversion)
$conn->executeStatement(
    'INSERT INTO events (scheduled_at) VALUES (?)',
    [(new DateTime('now', new DateTimeZone('UTC')))->format('Y-m-d H:i:s')]
);

Timezone Ambiguity in PHP:
- PHP’s DateTime defaults to the system timezone. Explicitly set the timezone when creating objects:
```
$date = new DateTime('now', new DateTimeZone('UTC')); // ✅ Explicit
$date = new DateTime(); // ❌ Uses system timezone (unpredictable)
```
- Use DateTimeImmutable for thread-safe operations.
Doctrine Events: The package hooks into prePersist, preUpdate, and preFlush events. Conflicts may arise with other listeners. Ensure your listeners run after DatabaseTimezoneListener by adjusting priority:
```
# config/services.yaml
App\EventListener\MyListener:
    tags:
        - { name: doctrine.event_listener, event: prePersist, priority: -10 } # Run after
```

Testing: Mock the DatabaseTimezoneListener in tests to avoid timezone-related flakiness:

$listener = $this->createMock(DatabaseTimezoneListener::class);
$listener->method('convertToDatabaseTimezone')->willReturnArgument(0);
$entityManager->getEventManager()->addEventListener([], $listener);

Tips

Debugging:

Log converted values to verify behavior:

$entityManager->getEventManager()->addEventListener([], new class {
    public function prePersist(LifecycleEventArgs $args) {
        $entity = $args->getObject();
        if (method_exists($entity, 'getScheduledAt')) {
            error_log('Saving: ' . $entity->getScheduledAt()->format('Y-m-d H:i:sP'));
        }
    }
});

Check the database directly to confirm stored values match expectations.

Performance:
- The package adds minimal overhead (~1ms per operation). Benchmark if using in high-frequency loops.

Extensions:

Add Support for Custom Types: Extend DatabaseTimezoneListener to handle non-standard Doctrine types (e.g., DateInterval):

public function convertToDatabaseTimezone($value, string $type): mixed
{
    if ($value instanceof DateInterval) {
        return $value->format('%d days %h hours %i minutes');
    }
    // ... existing logic
}

Timezone-Aware Serialization: Use the package with APIs by serializing DateTime objects with timezone info:

#[Serializer\SerializedName('scheduled_at', groups: ['api'])]
public function getScheduledAtApi(): string
{
    return $this->scheduledAt->format(DateTimeInterface::ATOM);
}

Configuration:

Validate the config timezone early in the app lifecycle (e.g., in a kernel event listener):

use DateTimeZone;

public function onKernelRequest(RequestEvent $event)
{
    $config = $this->container->getParameter('database_timezone.database');
    if (!in_array($config, DateTimeZone::listIdentifiers())) {
        throw new \RuntimeException("Invalid timezone: {$config}");
    }
}

Legacy Data:

Migrate existing data to the new timezone format:

$conn = $entityManager->getConnection();
$conn->executeStatement(
    'UPDATE events SET scheduled_at = CONVERT_TZ(scheduled_at, @@session.time_zone, "UTC")'
);

Database Timezone Laravel Package