Handling DateTimes-Cookbook is … error-prone regarding Timezones #5664

New Issue

admin · 2026-01-22T15:14:05+01:00

admin commented

2026-01-22 15:14:05 +01:00

Originally created by @heiglandreas on GitHub (Sep 1, 2017).

The DateTime-Cookbook favours to store all DateTimes by converting them to UTC and store them in the Database in UTC. That is a possible approach, but it can (and will) lead to problems as soon as the olson-DB changes. That will lead to differing offsets resulting in wrong DateTimes when hydrating them from the database.

I have not yet found a good solution using the ORM (that's why there isn't a PR by now) but I think it is unwise to give advice that can lead to errors without stating that.

For more information on the topic feel free to have a look at a blogpost I wrote

Originally created by @heiglandreas on GitHub (Sep 1, 2017). The DateTime-Cookbook favours to store all DateTimes by converting them to UTC and store them in the Database in UTC. That is a possible approach, but it can (and will) lead to problems as soon as the olson-DB changes. That will lead to differing offsets resulting in wrong DateTimes when hydrating them from the database. I have not yet found a good solution using the ORM (that's why there isn't a PR by now) but I think it is unwise to give advice that can lead to errors without stating that. For more information on the topic feel free to have a look at [a blogpost I wrote](https://andreas.heigl.org/2016/12/22/why-not-to-convert-a-datetime-to-timestamp/)

admin commented

2026-01-22 15:14:06 +01:00

@Ocramius commented on GitHub (Sep 1, 2017):

Can you suggest a storage format to be used?

On 1 Sep 2017 15:08, "Andreas Heigl" notifications@github.com wrote:

The DateTime-Cookbook favours to store all DateTimes by converting them to
UTC and store them in the Database in UTC. That is a possible approach, but
it can (and will) lead to problems as soon as the olson-DB changes. That
will lead to differing offsets resulting in wrong DateTimes when hydrating
them from the database.

I have not yet found a good solution using the ORM (that's why there isn't
a PR by now) but I think it is unwise to give advice that can lead to
errors without stating that.

For more information on the topic feel free to have a look at a blogpost
I wrote
https://andreas.heigl.org/2016/12/22/why-not-to-convert-a-datetime-to-timestamp/

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/doctrine/doctrine2/issues/6661, or mute the thread
https://github.com/notifications/unsubscribe-auth/AAJakFPBbKap45MashlzupkEPLf7j-jKks5seAGugaJpZM4PKIID
.

@Ocramius commented on GitHub (Sep 1, 2017): Can you suggest a storage format to be used? On 1 Sep 2017 15:08, "Andreas Heigl" <notifications@github.com> wrote: > The DateTime-Cookbook favours to store all DateTimes by converting them to > UTC and store them in the Database in UTC. That is a possible approach, but > it can (and will) lead to problems as soon as the olson-DB changes. That > will lead to differing offsets resulting in wrong DateTimes when hydrating > them from the database. > > I have not yet found a good solution using the ORM (that's why there isn't > a PR by now) but I think it is unwise to give advice that can lead to > errors without stating that. > > For more information on the topic feel free to have a look at a blogpost > I wrote > <https://andreas.heigl.org/2016/12/22/why-not-to-convert-a-datetime-to-timestamp/> > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > <https://github.com/doctrine/doctrine2/issues/6661>, or mute the thread > <https://github.com/notifications/unsubscribe-auth/AAJakFPBbKap45MashlzupkEPLf7j-jKks5seAGugaJpZM4PKIID> > . >

admin commented

2026-01-22 15:14:07 +01:00

@heiglandreas commented on GitHub (Sep 1, 2017):

Best storage-format IMHO would be local datetime and timezone in 2 separate fields. Important IMHO is that the datetime is not converted to UTC but left in local time. But that would require 2 DB-fields for one PHP-Object. dehydration would be like this:

function dehydrate(DateTimeInterface $datetime) 
{
    $dtStore = $datetime->format($platform->getDateTimeFormatString());
    $tzStore = $datetime->getTimezone()->getName();
}

and hydration on the other side would be like this:

function hydrate(string $dtStore, string $tzStore) : DateTime
{
    return new DateTime($dtStorage, new DateTimeZone($tzStorage));
}

That would also allow users to use in-DB timezone handling as I explained in these articles for Postgres and MySQL

Currently I have no clue as to how one could bring that into Doctrine. I'm happy for all pointers on where to start or how to achieve that…

@heiglandreas commented on GitHub (Sep 1, 2017): Best storage-format IMHO would be *local* datetime and timezone in 2 separate fields. Important IMHO is that the datetime is not converted to UTC but left in local time. But that would require 2 DB-fields for one PHP-Object. dehydration would be like this: ```php function dehydrate(DateTimeInterface $datetime) { $dtStore = $datetime->format($platform->getDateTimeFormatString()); $tzStore = $datetime->getTimezone()->getName(); } ``` and hydration on the other side would be like this: ```php function hydrate(string $dtStore, string $tzStore) : DateTime { return new DateTime($dtStorage, new DateTimeZone($tzStorage)); } ``` That would also allow users to use in-DB timezone handling as I explained in these articles for [Postgres](https://andreas.heigl.org/2016/05/29/timezones-and-postgresql/) and [MySQL](https://andreas.heigl.org/2016/04/18/timezones-and-mysql/) Currently I have no clue as to how one could bring that into Doctrine. I'm happy for all pointers on where to start or how to achieve that…

admin referenced this issue

2026-01-22 16:05:05 +01:00

[PR #5664] Fixed Filter Condition for inverse side #9681

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5664