Timezone issues (compendium of, various issues combined)

[petecooper]

trafficstars

I've combining 4-5 existing issues into one for housekeeping reasons.

Originally opened by @gocom at Google Code and imported at https://github.com/textpattern/textpattern/issues/405 https://github.com/textpattern/textpattern/issues/406 https://github.com/textpattern/textpattern/issues/407

Selecting timezone identifiers

• [ ] Make sure timezone identifier is always selected.
• [ ] If not, default to the server default timezone.
• [ ] If server default timezone isn't set, try to pick one.
• [ ] Set server default timezone with the timezone pref.

Unrelated timezone issues

• [x] Ability to set UTC as a valid timezone (see https://github.com/textpattern/textpattern/issues/1265)

Date calculations

• [ ] Drop is_dst and auto_dst options.
• [ ] And use the actual timezone data for date calculations.

Saving and selecting dates

• [ ] Make sure all dates are generated by the PHP application, not MySQL.
• [ ] Save dates to database in specific timezone such as UTC.

Issues relating to Saving and selecting dates:

• Conversion. Somehow convert the current dates to UTC (if any).
• URLs: how to keep current URLs intact (if any).

[petecooper]

@Bloke @philwareham Please apply Component: core to the labels as per the original issues. Thanks!

Also, original issues can be safely closed as they are are now merged here.

[bloatware]

It looks like most (all?) dates are stored in db as UTC timestamps, which is great. But we have two timezones interfering: the server default one (if set) and txp timezone_key pref. Which results in strftime() and safe_strftime() (both used in core) producing different results. I wonder why we don't simply set the default timezone to our pref? What is the point of keeping both?

[Bloke]

A good question. My understanding - and this is an educated guess - is that our timezone_key is for displaying dates on the public site. So, when we fetch things and wrangle stuff by date in the URL, that's the date which gets displayed: UTC stored date +/- the timezone_key.

The server default timezone is just that: the timezone of the server, which may be physically separate from where your intended audience(s) are. This timezone is used when storing all dates in UTC, and thus applying the correct offset when data is received from a browser that may be in a different timezone to the server.

Example:

• Server in San Francisco (default timezone = GMT-8) which houses three separate website domains (aka Textpattern instances):
• example.co.uk: timezone_key = GMT
• example.com: timezone_key = GMT-5 (for argument's sake)
• example.eu: timezone_key = GMT+1

As far as I know, that's why the two (safe_)strftime calls return different values, but I may be way off.

[bloatware]

Yes, but users date input is expected to respect timezone_key zone (they don't even have an idea of what the server tz is). Why wouldn't we just set the default timezone to timezone_key and use strtotime() to convert dates to UTC directly, rather then calculate the server/pref offset and then juggle with

    $ts = strtotime($time_str);

    // tz_offset calculations are expensive
    $tz_offset = tz_offset($ts);

    return strtotime($time_str, time() + $tz_offset) - $tz_offset;

I can not see where would we need to input a San Francisco date if our audience is in London.

[bloatware]

I have actually tried it, and it worked fine on my localhost, but some articles year/month/date URL were not recognized on the demo server.

[Bloke]

Why wouldn't we just set the default timezone to timezone_key

Which timezone_key? If hosting multiple domains on a single server, doesn't that mean flipping the server default setting back and forth every request? Won't they clash if multiple users were editing data on the CMS attached to both domains?

Unless I've got my wires crossed. The default timezone you're talking about is the system timezone setting, right? Inside php.ini. Or do you mean a default timezone that's under our control inside a Txp instance? If the latter then, yes, I agree all the +tz_offset - tz_offset nonsense seems superfluous.

[bloatware]

Oh, I didn't mean altering php.ini, just calling date_default_timezone_set(), which seems to act per session, like setlocale() etc (it would be too bad otherwise).

Well, there is no real problem to use both server and site timezones, it just needs more attention. A more important issue is db server timezone, which can be different yet. Not sure we take it into account everywhere.

[Bloke]

Ah yes, sorry for being dim. In which case, I expect date_default_timezone_set() is probably the winner. That function didn't exist when Txp was born in PHP 4 days, which is probably why we had to use all these workarounds. By modernising and using that function, we could probably save a craptonne of hacks.

Not sure about db server timezone. It makes my brain hurt when the server and database are physically separated like that.

[bloatware]

Okay, let's try it. I had no issues on localhost, but Pete's server has 2h db/server tz offset, so some articles published near midnight go west. Investigating.

This actually happens in 'double' tz setup too, so must be definitely related to db tz offset.

[petecooper]

Demo server is UTC, database should follow geolocation (check phpinfo(), it's accurate).

[bloatware]

Yes, db is UTC, but not php:

Server local time: 2021-08-06 00:01:00
...
Time zone (GMT offset in seconds): Europe/Amsterdam (3600)
...
Database server time: 2021-08-05 22:01:00

This is not forbidden, per se, but makes that an article published at 2021-08-06 00:01:00 (php time) is saved in db with 2021-08-05 22:01:00 datetime value, which makes its URL invalid in year/month/date scheme. Additionally, you must do some rewrites that make it fail even more (I would like to know the details).

The problem will appear on any db server that is in a different tz than php. Individual URLs should be recognized, but search by 2021/08/06/ will fail.

[petecooper]

Strange. I haven't changed the database time at all, I just set the server to UTC and then let the other stuff do its own thing with appropriate geo stuff applied. It's all on the same host, so I'm not really sure why there's a two-hour offset.

is this db offset problematic? I'm very happy to change it if you'd like me to, it makes no difference from my point of view. I suppose there's some value in it being a bit…weird.

[Bloke]

@petecooper I'd leave it for now. As you say, it's a good test of functionality to check that we can handle situations where the DB is physically separate in a different timezone. There are hosts that do that. Well, splitting the DB to its own server, not so sure about in a different timezone but in theory it's possible.

[bloatware]

Db offset is quite legit, but problematic in year/month/day URL mode. We must fix it anyway, so please leave it as is.

[bloatware]

SET GLOBAL time_zone = '-02:00' if you fancy local testing.

[bloatware]

A quick fix looks easy via CONVERT_TZ or DATE_ADD SQL functions, once php/sql timezones are compared, but they do not use the same conventions: what is SQL SYSTEM tz for PHP, especially if they are on different servers?