timostamm / doctrine-fixed-timezone
Normalize timezones in a Doctrine database.
Installs: 18 762
Dependents: 0
Suggesters: 0
Security: 0
Stars: 2
Watchers: 3
Forks: 1
Open Issues: 3
Requires
- php: ^7.1|^8.0
- doctrine/dbal: ^2.7
Requires (Dev)
- phpunit/phpunit: ^7.5
README
The problem
If your application handles dates with different timezones, you have two options:
1) save the time zone of each date to the database
If you need to know the timezone later on, this is the only option.
Unfortunately, this makes queries on dates practically impossible:
SELECT * FROM x WHERE x.date > CURRENT_TIMESTAMP() will lead to
unexpected results. CURRENT_TIMESTAMP will use the current time of
the database server, and your dates are interpreted in the wrong
timezone.
2) Convert all dates saved in the database to one timezone
If you convert all dates to the timezone of the database server, your queries will work as expected.
Unfortunately, Doctrine does not come with an option to do this conversion.
Solution provided by this library
This library provides replacements for the DBAL types datetime,
and datetime_immutable, which will automatically convert dates
into the database timezone.
When reading a datetime or datetime_immutable, the timezone
will not be converted to the PHP timezone. The dates will refer
to the correct point in time, but have the database timezone.
Regarding standalone date and time
A date without a time, or a time without a date both do not refer
to an absolute point in time. Since PHP does not have types to
represent stand-alone times or dates, developers use DateTime
objects to represent those too, but their timezone should be ignored.
Therefore, it should be fine to use the standard date and time
doctrine types.
How to use
Symfony:
doctrine: dbal: # ... types: datetime: TS\DoctrineExtensions\DBAL\FixedDbTimezone\DateTimeType datetime_immutable: TS\DoctrineExtensions\DBAL\FixedDbTimezone\DateTimeImmutableType
To set the database timezone, define a constant
define('DATABASE_TIMEZONE', 'Europe/Berline'); or
set an environment variable DATABASE_TIMEZONE.
Otherwise, the PHP timezone is used.