检索客户时区MySQL的自然时间间隔

Question

我具有带有时区支持的PHP-MYSQL API。我的数据库位于UTC中，所有日期时间计算都是在UTC时间中进行的，只是在向用户显示数据时转换为本地时间。

问题是在尝试在不同时区的用户以自然时间间隔检验数据时，例如数周，几个月，年等。DB列是类型DateTime，并在UTC中存储日期。如果我需要按月分组的所有行，并且只需使用存储在数据库中的UTC日期，则如果时区差异使一些行变为一个月，我将遇到一些错误的行。

。

示例：与Value 2015-05-01 00:00:00的行在UTC中，但应在负时区域中的任何用户4月。

因此，使用UTC不是这里的解决方案。我需要首先将这些日期转换为客户时区。

我可以用哪种侵犯来克服这个问题？

作为一个例子，这是一周的视图组：

SELECT `individuals_id`, 
Str_to_date(Concat(Yearweek(`sessions_date`, 5), ' Monday'), '%X%V %W') AS `sessions_date`,
`protocol_index`, 
`zone_index`, 
Avg(`asymmetry_dom`) AS `asymmetry_dom`, 
Avg(`asymmetry_rl`) AS `asymmetry_rl`, 
FROM `sessions_data_view` 
GROUP  BY `individuals_id`, 
          Yearweek(`sessions_date`, 5), 
          `protocol_index`, 
          `zone_index` 
ORDER  BY `individuals_id`, 
          Yearweek(`sessions_date`, 5), 
          `protocol_index`, 
          `zone_index` 

问题在于，根据用户时区，Yearweek()应该对行具有不同的输出。如果要为用户给出一致的结果，则不适合在UTC中使用列sessions_date。

rigth现在我不知道用户时区，但这不应该是一个限制，因为该应用程序处于降阶段，并且可以更改任何内容。

API是获得HTTP请求的PHP应用程序。它与PHP数据库类交谈，该类将所有查询都包含到MariadB数据库中。API的所有响应均为JSON，将日期形成为UTC字符串。数据通过Web应用程序显示。DateTime JavaScript对象负责将响应从API转换为纠正客户端时区的日期。

Answer 1

首先，假设他们的计算机的时间安排是正确的，那么获得客户端时区的唯一可靠方式就是通过JavaScript。

解决方案一个-JavaScript到PHP

我建议使用以下JavaScript收集用户时区：

var timeZone = new Date().getTimezoneOffset();

您需要将此值存储在数据库中。然后，您将UTC时间与PHP代码类似：

类似

$date = new DateTime();
$timezone = new DateTimeZone('Australia/Sydney');
$date->setTimezone($timezone);
$formatted_date = $date->format('Y-m-d H:i:s');

解决方案两个-PHP到JavaScript

您将UTC字符串转换为UNIX TIMESTAMP，其中以下内容：

$time = strtotime($utc);

然后，您将其返回到浏览器，并具有这样的JavaScript格式：

var date = new Date(timestamp * 1000),
    datevalues = [
       date.getFullYear(),
       date.getMonth()+1,
       date.getDate(),
       date.getHours(),
       date.getMinutes(),
       date.getSeconds(),
    ];

祝你好运！

编辑：用于转换MySQL用于查询，使用CONVERT_TZ，格式为：

CONVERT_TZ('date/time field','starting timezone','ending timezone')

例如：

select *, DATE_FORMAT(CONVERT_TZ(str_to_date(timestamp),'UTC','US/Eastern'), '%m/%d/%y %h:%i %p') as 'Date - Eastern' FROM table`