在PHP和MySQL中处理时区 [英] Dealing with timezones in PHP and MySQL

问题描述

我试图了解到底是什么分别负责更改和转换应用程序堆栈中的时区和时间，以及什么是将时间转换为应用程序代码中所需时区的最佳方法.

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.

我当前正在使用:

Javascript/HTML字体结尾

Javascript/HTML font end

Laravel PHP框架服务器端

Laravel PHP framework server side

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

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

我认为:

MySQL无法为DateTime列存储时区，而仅存储YYY:MM:DD hh:mm:ss，开发人员需要将时区存储在单独的列等中，并将存储的时间转换为本地用户如果用户时区不同，则为应用程序代码.

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.

PHP Laravel应用程序应该可以运行，并将任何DateTime实例转换为使用date_default_timezone_set()函数设置的时区.

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

我当前正在观察的行为:

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

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)'

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

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).

我的应用程序将此UTC调整后的时间存储在数据库中，而实际上没有任何记录是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.

使用雄辩或DB:query检索记录时，不会自动转换为用户时区，它只是返回存储时的时间(转换为UTC)，需要应用程序代码将其转换为正确的时区，否则为用户(以澳大利亚为例)将关注UTC时间，而不是本地的AEST Etc.

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.

转换时间:

是否存在一种简便的方法，可以自动将所有从数据库中检索到的UTC时间转换为用户本地时区，或者如果假定所有用户都来自同一时区，则将其转换为某些指定的默认时区吗?

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 ?

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

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. ?

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

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 ?

推荐答案

时区很烦人，这毫无疑问.如果我对您的理解正确，那么您希望PHP将时间返回到用户正确区域中的视图，对吗?

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?

我所要做的是在主视图"中或某种要保证至少被加载一次的sort或blade.php文件中，我检查此用户的时区是否存储在会话变量中.如果不是，我向服务器发送AJAX请求以存储时区的名称.

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.

{{-- 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

请注意，这种方法利用了jstz软件包，您可以在此处下载并包含在您的< head> 部分.

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

当然，您需要为此请求设置路由，就我而言，它看起来像这样:

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'));
    }
});

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

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);

通常，我建议您保留所有以UTC格式存储的日期，因为这是标准的做法，因此数据库必须保持与时区无关.但是，如果要更改默认值，则可以编辑'timezone'=>行. config/app.php 中的"UTC" .这将覆盖Laravel默认为其设置时间戳的区域，因此您的created_at，updated_at将被更改以反映该新时区.

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.

这篇关于在PHP和MySQL中处理时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！