处理PHP和MySQL中的时区

时间:2022-10-28 20:44:54

I am trying to understand what exactly is responsible for changing and converting timezones and time respectively in my application stack and what is the best way to go about converting time to the required timezone in application code.

我试图了解在我的应用程序堆栈中分别更改和转换时区和时间的确切原因是什么,以及将时间转换为应用程序代码中所需时区的最佳方法。

I am currently using:

我目前正在使用:

Javascript/HTML font end

Javascript / HTML字体结束

Laravel PHP framework server side

Laravel PHP框架服务器端

MySQL storage (Defaults to local system time for timestamps etc.)

MySQL存储(默认为本地系统时间的时间戳等)

I am of the opinion that:

我认为:

MySQL can't store timezones for DateTime columns and just stores YYY:MM:DD hh:mm:ss and it's up to the developer to store the timezone in a separate column etc. and convert the stored time to user local in application code if user timezone is different.

MySQL无法为DateTime列存储时区,只存储YYY:MM:DD hh:mm:ss,由开发人员将时区存储在单独的列等中,并将存储的时间转换为应用程序代码中的用户本地用户时区不同。

A PHP, Laravel application should work in and convert any DateTime instances to the timezone set with date_default_timezone_set() function.

PHP,Laravel应用程序应该工作并将任何DateTime实例转换为使用date_default_timezone_set()函数设置的时区。

The behaviour I am currently observing:

我目前正在观察的行为:

I post back json with object properties set with javascript date time format. This format looks like so for me: e.g. 'Thu Jun 16 2016 18:00:00 GMT+1000 (AEST)'

我用javascript日期时间格式设置对象属性回发json。这种格式对我来说是这样的:例如'2016年6月16日星期四18:00:00 GMT + 1000(AEST)'

When this json data hits my server, the application framework or PHP automatically converts it to UTC, even if I put date_default_timezone_set('Australia/Sydney') in controller class or change application configuration, replacing the laravel UTC default. I suspect something is not registering ? If I was able to save time as it came from the client (without being automatically converted to UTC), I would not have to later convert it to the user local time zone if the user is in Australia/Sydney (which is most of them).

当这个json数据到达我的服务器时,应用程序框架或PHP会自动将其转换为UTC,即使我将date_default_timezone_set('Australia / Sydney')放入控制器类或更改应用程序配置,替换了laravel UTC默认值。我怀疑有些东西没有注册?如果我能够节省来自客户端的时间(不会自动转换为UTC),如果用户位于澳大利亚/悉尼(其中大部分是用户),我将不必在以后将其转换为用户本地时区)。

My application stores this UTC adjusted time in the database, without actually having any record that it is UTC. Not really important of course as I can presume default of UTC.

我的应用程序将此UTC调整时间存储在数据库中,而实际上没有任何记录它是UTC。当然不是很重要,因为我可以假设UTC的默认值。

When records are retrieved with eloquent or DB:query, there's no automatic conversion to user timezone and it simply returns the time as it was stored (converted to UTC), requiring application code to convert it to correct timezone, else user (in Australia in my case) will be looking at UTC time as opposed to local AEST Etc.

当使用eloquent或DB:查询检索记录时,没有自动转换为用户时区,它只返回存储时间(转换为UTC),需要应用程序代码将其转换为正确的时区,否则用户(在澳大利亚)我的情况)将查看UTC时间而不是本地AEST等。

Converting time:

Is there a hassle free way of automatically converting all retrieved UTC time from the database to the user local timezone or some specified default time zone if all users are presumed from the same timezone ?

是否有一种无忧无虑的方法可以自动将所有检索到的UTC时间从数据库转换为用户本地时区或某些指定的默认时区(如果所有用户都是从同一时区推测出来的话)?

Ideally I want to pull out records from the database and have all the time properties adjusted to specified timezone, taking daylight savings into account. Would I have to run a for loop and convert/set each property with Carbon methods etc. ?

理想情况下,我想从数据库中提取记录,并将所有时间属性调整到指定的时区,同时考虑夏令时。我是否必须运行for循环并使用Carbon方法转换/设置每个属性等?

If the date_default_timezone_set('Australia/Sydney') is set and working properly, should PHP be automatically converting the time property on all those objects as they are retrieved from the database ? As well as not converting time data to UTC when it hits the server ?

如果date_default_timezone_set('Australia / Sydney')设置正常并且工作正常,那么PHP是否应该在从数据库中检索所有这些对象时自动转换time属性?以及在点击服务器时不将时间数据转换为UTC?

2 个解决方案

#1


1  

timezones are annoying, there's no doubt about that. If I'm understanding you correctly, you want your PHP to return times to the view that are in the correct zone for the user, right?

时区很烦人,毫无疑问。如果我正确理解您,您希望PHP将时间返回到用户正确区域中的视图,对吧?

What I do is within the 'master view' or some sort or blade.php file that is guaranteed to be loaded at least once, I check whether or not this user's timezone is stored in a session variable. If it is not, I send an AJAX request to the server to store the name of the timezone.

我所做的是在'主视图'或某种类型或blade.php文件中保证至少加载一次,我检查该用户的时区是否存储在会话变量中。如果不是,我向服务器发送一个AJAX请求来存储时区的名称。

{{-- store timezone in session variables --}}
@if (!Session::has('timezone'))
    <script>
        $(function () {
            var tz = jstz.determine();
            var data = {};
            if (typeof (tz) !== 'undefined') {
                data.timezone = tz.name();
            }
            if (!$.isEmptyObject(data)) {
                $.ajax({
                    type: "POST",
                    url: "{{ url('/api/v1/settings') }}",
                    beforeSend: function (request) {
                        request.setRequestHeader("X-CSRF-TOKEN", "{{ csrf_token() }}");
                    },
                    data: $.param(data),
                });
            }
        });
    </script>
@endif

Note that this approach utilizies the jstz package, which you can download here and include in your <head> section.

请注意,此方法使用jstz包,您可以在此处下载并包含在部分中。

Of course you will need to set up the route for this request, for my case, it looks like this:

当然,您需要为此请求设置路由,对于我的情况,它看起来像这样:

Route::post('api/v1/settings', function () {
    // Save the user's timezone
    if (Request::has('timezone')) {
        Session::put('timezone', Request::get('timezone'));
    }
});

Now, when you want to convert the given database datetime strings to the correct timezone, you can get the timezone by saying $tz = $request->session()->get('timezone') and then parse out the dates with Carbon\Carbon::parse($date, $tz);

现在,当您想将给定的数据库日期时间字符串转换为正确的时区时,您可以通过说$ tz = $ request-> session() - > get('timezone')来获取时区,然后使用Carbon解析日期\ Carbon :: parse($ date,$ tz);

In general, I would recommmend you stay with storing all dates in UTC format, as that is the standard and it is imperitive that the database remain timezone agnostic. But if you want to change the default, you can edit the line 'timezone' => 'UTC' in config/app.php. That will overwrite the zone that Laravel defaults its timestamps to, so your created_at, updated_at will be changed to reflect that new timezone.

一般来说,我建议您继续以UTC格式存储所有日期,因为这是标准,并且数据库保持时区不可知是不完美的。但是,如果要更改默认值,可以在config / app.php中编辑“timezone”=>“UTC”行。这将覆盖Laravel默认其时间戳的区域,因此您的created_at,updated_at将更改为反映新的时区。

#2


0  

Agh - the good old "what to do with timezones" issue. Personally, on one of my L5.2 projects that heavily requires user timezone formatting for displaying data. I allow the user to select/modify their timezone on an account settings page. I then store this in a database and set up relationships for retrieving. For instance, I have many users who belong to one company. So in my "companies" table, I have a timezone field here. With proper relationships, I can access it like so Auth::user()->company->timezone

唉 - 这个古老的“如何处理时区”问题。就个人而言,我的一个L5.2项目非常需要用户时区格式化来显示数据。我允许用户在帐户设置页面上选择/修改他们的时区。然后我将其存储在数据库中并设置检索关系。例如,我有很多用户属于一家公司。所以在我的“公司”表中,我在这里有一个时区字段。通过适当的关系,我可以像Auth :: user() - > company-> timezone那样访问它

Then I parse all dates relevant to the platform using Carbon. For example, $current_date = Carbon:parse($date, Auth::user()->company->timezone);.

然后我使用Carbon解析与平台相关的所有日期。例如,$ current_date = Carbon:parse($ date,Auth :: user() - > company-> timezone);.

In hindsight, if I wasn't lazy (have other priorities) and wouldn't need to update a bunch of code, I'd probably create a trait for my models to convert the date attributes automatically.

事后看来,如果我不是懒惰(有其他优先事项)并且不需要更新一堆代码,我可能会为我的模型创建一个特性来自动转换日期属性。

Honestly though, there's many ways - and it can be a tricky thing to figure out if your platform is heavily on user timezones.

老实说,有很多方法 - 弄清楚你的平台是否在很大程度上依赖用户时区,这可能是一个棘手的事情。

For example, you could do something like this in each of your models (or create a trait - best option - to store this). Note, I used my timezone relationship as an example. Obviously change "some_date" etc to whatever you table name is. Repeat as needed for each date you need converted:

例如,您可以在每个模型中执行类似的操作(或创建特征 - 最佳选项 - 存储此模型)。注意,我以时区关系为例。显然,将“some_date”等更改为您的表名称。根据需要重复您需要转换的每个日期:

/**
 * Convert date to user's timezone
 * 
 * @return mixed
 */
public function setSomeDateAttribute($value)
{
    $this->attributes['some_date'] = Carbon::parse($value, Auth::user()->company->timezone);
}

Note, dates should always be stored in database as UTC. Never save a user's timezone formatted date in database.

注意,日期应始终作为UTC存储在数据库中。切勿将用户的时区格式化日期保存在数据库中。

#1


1  

timezones are annoying, there's no doubt about that. If I'm understanding you correctly, you want your PHP to return times to the view that are in the correct zone for the user, right?

时区很烦人,毫无疑问。如果我正确理解您,您希望PHP将时间返回到用户正确区域中的视图,对吧?

What I do is within the 'master view' or some sort or blade.php file that is guaranteed to be loaded at least once, I check whether or not this user's timezone is stored in a session variable. If it is not, I send an AJAX request to the server to store the name of the timezone.

我所做的是在'主视图'或某种类型或blade.php文件中保证至少加载一次,我检查该用户的时区是否存储在会话变量中。如果不是,我向服务器发送一个AJAX请求来存储时区的名称。

{{-- store timezone in session variables --}}
@if (!Session::has('timezone'))
    <script>
        $(function () {
            var tz = jstz.determine();
            var data = {};
            if (typeof (tz) !== 'undefined') {
                data.timezone = tz.name();
            }
            if (!$.isEmptyObject(data)) {
                $.ajax({
                    type: "POST",
                    url: "{{ url('/api/v1/settings') }}",
                    beforeSend: function (request) {
                        request.setRequestHeader("X-CSRF-TOKEN", "{{ csrf_token() }}");
                    },
                    data: $.param(data),
                });
            }
        });
    </script>
@endif

Note that this approach utilizies the jstz package, which you can download here and include in your <head> section.

请注意,此方法使用jstz包,您可以在此处下载并包含在部分中。

Of course you will need to set up the route for this request, for my case, it looks like this:

当然,您需要为此请求设置路由,对于我的情况,它看起来像这样:

Route::post('api/v1/settings', function () {
    // Save the user's timezone
    if (Request::has('timezone')) {
        Session::put('timezone', Request::get('timezone'));
    }
});

Now, when you want to convert the given database datetime strings to the correct timezone, you can get the timezone by saying $tz = $request->session()->get('timezone') and then parse out the dates with Carbon\Carbon::parse($date, $tz);

现在,当您想将给定的数据库日期时间字符串转换为正确的时区时,您可以通过说$ tz = $ request-> session() - > get('timezone')来获取时区,然后使用Carbon解析日期\ Carbon :: parse($ date,$ tz);

In general, I would recommmend you stay with storing all dates in UTC format, as that is the standard and it is imperitive that the database remain timezone agnostic. But if you want to change the default, you can edit the line 'timezone' => 'UTC' in config/app.php. That will overwrite the zone that Laravel defaults its timestamps to, so your created_at, updated_at will be changed to reflect that new timezone.

一般来说,我建议您继续以UTC格式存储所有日期,因为这是标准,并且数据库保持时区不可知是不完美的。但是,如果要更改默认值,可以在config / app.php中编辑“timezone”=>“UTC”行。这将覆盖Laravel默认其时间戳的区域,因此您的created_at,updated_at将更改为反映新的时区。

#2


0  

Agh - the good old "what to do with timezones" issue. Personally, on one of my L5.2 projects that heavily requires user timezone formatting for displaying data. I allow the user to select/modify their timezone on an account settings page. I then store this in a database and set up relationships for retrieving. For instance, I have many users who belong to one company. So in my "companies" table, I have a timezone field here. With proper relationships, I can access it like so Auth::user()->company->timezone

唉 - 这个古老的“如何处理时区”问题。就个人而言,我的一个L5.2项目非常需要用户时区格式化来显示数据。我允许用户在帐户设置页面上选择/修改他们的时区。然后我将其存储在数据库中并设置检索关系。例如,我有很多用户属于一家公司。所以在我的“公司”表中,我在这里有一个时区字段。通过适当的关系,我可以像Auth :: user() - > company-> timezone那样访问它

Then I parse all dates relevant to the platform using Carbon. For example, $current_date = Carbon:parse($date, Auth::user()->company->timezone);.

然后我使用Carbon解析与平台相关的所有日期。例如,$ current_date = Carbon:parse($ date,Auth :: user() - > company-> timezone);.

In hindsight, if I wasn't lazy (have other priorities) and wouldn't need to update a bunch of code, I'd probably create a trait for my models to convert the date attributes automatically.

事后看来,如果我不是懒惰(有其他优先事项)并且不需要更新一堆代码,我可能会为我的模型创建一个特性来自动转换日期属性。

Honestly though, there's many ways - and it can be a tricky thing to figure out if your platform is heavily on user timezones.

老实说,有很多方法 - 弄清楚你的平台是否在很大程度上依赖用户时区,这可能是一个棘手的事情。

For example, you could do something like this in each of your models (or create a trait - best option - to store this). Note, I used my timezone relationship as an example. Obviously change "some_date" etc to whatever you table name is. Repeat as needed for each date you need converted:

例如,您可以在每个模型中执行类似的操作(或创建特征 - 最佳选项 - 存储此模型)。注意,我以时区关系为例。显然,将“some_date”等更改为您的表名称。根据需要重复您需要转换的每个日期:

/**
 * Convert date to user's timezone
 * 
 * @return mixed
 */
public function setSomeDateAttribute($value)
{
    $this->attributes['some_date'] = Carbon::parse($value, Auth::user()->company->timezone);
}

Note, dates should always be stored in database as UTC. Never save a user's timezone formatted date in database.

注意,日期应始终作为UTC存储在数据库中。切勿将用户的时区格式化日期保存在数据库中。