Handling DateTimes-Cookbook is … error-prone regarding Timezones

[heiglandreas]

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

[Ocramius]

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 .

[heiglandreas]

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…