building timezone feature in PHP web application

building timezone feature in PHP web application

柳若烟 发布于 2021-11-29 字数 1294 浏览 839 回复 3 原文

This is almost similar question to this one: -

Dealing with timezones in PHP

I read that thread, and have some more issues, and not sure of how to elegantly build timezone support in PHP/MySQL application.

I am writing an application in PHP, where users can select their timezone.

Here, all timestamps are stored in GMT in MySQL. And I am converting the timezone back to users timezone, when presenting the data.

Now these are the problems I am facing: -

I show the user, a list of timezones populated from MySQL's timezone tables, it is a HUGE list and is difficult to chose from.

So, I want to implement something like windows time zone list. Offset with region info.

The PHP and MySQL may have different set of list.

Where should I convert timezones, in PHP or in MySQL? I have combination of coding.
Sometimes it is easy to select a column converted from MySQL, sometimes it is easy to convert in PHP.
So, here the previous point becomes very important. For example, in MySQL's time_zone tables
Asia/Kolkata, and Asia/Calcutta both were there, but in PHP 5.2.6's default timezonedb, only Asia/Calcutta is present.

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。



需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。


ま柒月 2022-06-07 3 楼

This is the sort of situation where a custom class might be the best solution. Then you can move times and dates around with their timezones. Luckily, PHP v5 has just such an object built-in. You just have to remember to code things so that when you rely on an implicit timezone, it's because you know you've earlier set it explicitly. This also means if you don't know what the implicit value is, you go ask, effectively making it explicit.

Looking at PHP's timezone support and MySQL's timezone support, I would do timezone stuff largely in PHP. MySQL's support is largely oriented around it's concept of "now".

Oh yes: you do not want to do explicit date arithmetic (e.g. adding 86400 seconds to add "one day"). Let the PHP object or libraries do that. They will have bugs fixed you will not have thought of. (I once wrote an object for a specialized type of date manipulation and spent weeks battling an intermittent off-by-one-hour bug, until I discovered the one spot that was adding 86400 to make a day transition instead of using mktime(). It turned out that daylight-saving rules were messing things up. Which, of course, I wasn't checking for.)

霞映澄塘 2022-06-07 2 楼
  1. Choosing a time zone from a large list isn't that difficult. Sort the list by standard-time offset so that users can quickly scroll to the correct offset, and from there, look for their exact location.

  2. Choose one list. I would tend to go for whichever list is shorter. Only present those choices to the user.

  3. Do the math where the list that was chosen is. If you're presenting the MySQL list, have MySQL do the date math, if you're using PHP's list, have PHP do the math. This will make so that you don't need to figure out some weird mapping for missing zones in one implementation.

A fancy solution could involve asking the user's location and extrapolating their timezone. (However, have the option to explicitly set it as well)

我乃一代侩神 2022-06-07 1 楼

When faced with this exact issue, I found this reference, which maps the succinct, Windows-style timezone list to a subset of the ridiculously exhaustive Unix-style timezone list.

Users are presented with a dropdown of these windows-style names (e.g., (GMT-05:00) Eastern Time (US & Canada)), and their selection is stored in the db in the unix-style format (e.g. America/New_York)

The work of applying the user's timezone preference is done in PHP at display time, using the DateTime class. I think I'd recommend this, so you can be confident that the dates you're manipulating in SQL/PHP are always in UTC, until they're displayed.