[META] Add timezone support to core date fields

Thanks @dww and others for taking the helm here to keep this moving. Reading the original post brought back a little PTSD, as that started the cascade of uncovering a lot of TZ related bugs.

Plan seems great.

as that started the cascade of uncovering a lot of TZ related bugs.

Just a ignorant set of questions, because I didn't follow any of the other issues, but:

• Are you storing all dates using UTC ? You probably should.
• timestamp with time zone is SQL-92 standard, but as I recall, only PostgreSQL implements it, do you plan to use it ?
• MySQL own timestamp type (which is different from datetime) is supposed to behave the same, i.e. converting all dates from the client set time zone to UTC when storing, do you plan on using this ?
• Actually you probably should not use MySQL timestamp type, it will have the same limitations as UNIX timestamps (i.e. you can't set date before 1970's)
• My guess is that proper date and time zone support should be in the database query builder directly, at least you wouldn't have to worry about it on the PHP side, but for this it should be extended to properly hydrate SQL result to native PHP type (I don't know if that was done or not, didn't do Drupal 8 for a long time).
• Time zone handling is very simple to understand, very hard to get it right, especially because each layer your dates goes throught will at some point attempt time zone offset conversion.
• If you don't don't store all your dates in the database using the same reference time zone (UTC is the right one for the job) you're going to have side effects when selecting into the database, false positives or false negatives when comparing dates.

My guess is that until the database layer doesn't handle the time zone itself, you will have bugs forever. PHP \DateTime[Immutable] objects handle them well, the hardest part is to create the instances with the right time zones directly: you must always specify the time zone when creating your instances, or you'll have time shift bugs when converting.

I just had to solve all those dates problems on a proprietary production project, spent 3 days working on the matter, with a custom (very close to Drupal's one) database access layer and query builder, MySQL and PostgreSQL support, many time zone related bugs, I'm quite sure you experienced all the same problems I had.

Are you storing all dates using UTC ? You probably should.

Timestamps (entity created / updates) and Unix timestamps as int. Datetime are ISO8601 strings w/o offset, and implicitly UTC. Both are legacy decisions.

timestamp with time zone is SQL-92 standard, but as I recall, only PostgreSQL implements it, do you plan to use it ?

Datetime are ISO8601 as strings b/c of this, and the lack of standardization across database backends. And the database backend adapter was added after this decision. This has the potential of being a huge contrib break because of the assumptions you can make with the strings.

MySQL own timestamp type (which is different from datetime) is supposed to behave the same, i.e. converting all dates from the client set time zone to UTC when storing, do you plan on using this ?

Same as above.

Actually you probably should not use MySQL timestamp type, it will have the same limitations as UNIX timestamps (i.e. you can't set date before 1970's)

Most time_t implementations are signed, which let you get back to about 1901 with a signed 32-bit. The 2038 problem is the rollover of a signed 32. Once you get into PHP, you are dealing w/ unix timestamps at the lowest level (\DateTime is a fancy wrapper for them). Core has test coverage for dates outside the 1901-2038 range.

My guess is that proper date and time zone support should be in the database query builder directly, at least you wouldn't have to worry about it on the PHP side, but for this it should be extended to properly hydrate SQL result to native PHP type (I don't know if that was done or not, didn't do Drupal 8 for a long time).

A time machine would be great, but a lot of the these problems were discovered after Drupal 8 beta freeze, and BC support is very hard especially when a lot of these bugs are interconnected.

Time zone handling is very simple to understand, very hard to get it right, especially because each layer your dates goes throught will at some point attempt time zone offset conversion.

The biggest problem was realizing that testbot also ran in UTC, so a lot of test coverage was bad, which took a while to fix.

If you don't don't store all your dates in the database using the same reference time zone (UTC is the right one for the job) you're going to have side effects when selecting into the database, false positives or false negatives when comparing dates.

Yeah, but that isn't what this issue is about. Not storing the IANA TZ name w/ the value throws away information. An ISO string can give you the offset, but there are a myriad of quirks when you really care about wall time based on where you are (and the DST mess).

Most time_t implementations are signed, which let you get back to about 1901 with a signed 32-bit. The 2038 problem is the rollover of a signed 32. Once you get into PHP, you are dealing w/ unix timestamps at the lowest level (\DateTime is a fancy wrapper for them). Core has test coverage for dates outside the 1901-2038 range.

That's still a pretty rough limitation ! Timestamp should probably not be used, I know that it's a Drupal choice since the beginning so I will not argue about it, it's what it is for now, nevertheless in the long term, it should be good to get rid of those timestamps everywhere, including in node creation and update timestamp.

A time machine would be great, but a lot of the these problems were discovered after Drupal 8 beta freeze, and BC support is very hard especially when a lot of these bugs are interconnected.

Wasn't it the goal to add interfaces and abstractions everywhere ? To be able to replace system parts without the need to rewrite all. But I agree, easier said than done.

The biggest problem was realizing that testbot also ran in UTC, so a lot of test coverage was bad, which took a while to fix.

I had to write those kind of unit tests too, preparing the environment by changing default time zone at runtime in tests is something doable.

Not storing the IANA TZ name w/ the value throws away information.

That's a topic I had (very) long thoughts about, but in the end, it doesn't matter. Or should I say, it depends on your business, but most of the time, you don't care about storing the political zone name. It's a political information, not a time information, UTC is the absolute date the only thing you need to do proper conversions for display. After years worrying about this (I'm working on business proprietary software) storing the time zone has never been a requirement in the end. Storing a time zone or a time offset only gives you the information about where was the guy who input the date, but it doesn't give you anything more. Time zone is important for display, not for storage.

and the DST mess

It took me a while to figure it out, but DST is not a mess. If you store UTC, every piece of library in the world will handle that for you, PostgreSQL does, PHP \DateTime objects do, etc. It works because those library will implement conversions using the IANA TZ database and standard. Of course, that's bugs put aside, but bugs should be fixed in your library you use, not in your code. Any attempt in writing workaround in your business code will eventually end up in catastrophic failure.

and the DST mess

Hi, apologies if this is not the correct place to report bugs. We applied the patch in comment #277 of https://www.drupal.org/node/2632040 to my Drupal 8.9. But there seems to be an issue with DST and date range fields. We've set to allow overriding timezone per field instance on a specific date range field.

We are noticing that if we set the start date in daylight savings timezone (e.g. 1st January 2021 at 20:00:00 America/NewYork), the end date in Summer timezone (e.g. 1st May 2021 at 20:00:00 America/NewYork), and we're setting "America/New York" timezone for that specific field (issue is not exclusive to this TZ I imagine), then the end date is not converted correctly when saved to the database. The end date with above example should be saved as 2nd May 00:00:00 UTC (converted from 1st May 20:00:00 America/NewYork : +4 hours) but what we're seeing is it's saved as 2nd May 01:00:00 UTC (+5 hours : 1 extra hour wrongly added which seems to come from DST) in the database table. The start date is converted correctly.

If we set the start date to be in the summer time also, then both the dates are saved correctly with +4 hours offset added.

I tried to step through the code to find the cause but I'm not familiar enough with fields and where exactly they're converted and saved etc so ran out of time for this, and decided to leave a bug report.

At first it seemed like an issue when loading the data + converting timezones, but it looks like it's actually saved wrong into the database. What ends up happening is everytime a user edits a node with such date setup, 1 hour is added to the end date without user input/changing the dates. So 5 edits to the node, ends up adding 5 hours to the end date. This seems like a major bug

Currently the workaround seems to be to just use "UTC" timezone override for such a wide date range.

There is this option: https://www.drupal.org/project/datetime_range_timezone