找到你要的答案

Q:Retrieve Natural Time Intervals in MySQL for the client Time Zone

Q:获取客户端的时区,MySQL自然时间间隔

I have a PHP-MySQL API with Time Zone Support. My database is in UTC and all date time calculations are made in UTC time, just converting to local time when data is displayed to the user.

The problem is when trying to retrive data in a natural time interval for a user in a different time zone, for example weeks, months, years and so on. The DB column is type DateTime and store dates in UTC. If I need all rows grouped by month and just use the UTC date stored in the database I will get some wrong rows, if the Time Zone differences made some rows shift its month.

Example: row with value 2015-05-01 00:00:00 is in May in UTC, but should be in April for any user in a negative Time Zone.

So using UTC is not the solution here. I need first to convert those dates to client Time Zone.

Which aproach can I use to overcome this problem?

As an example this is view groups some data by week:

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` 

The problem is that Yearweek() should have different output for a row depending on the user time zone. It is not posible to use the column sessions_date in UTC if want to give consistent result to the user.

Rigth now I do not know the user Time Zone, but this should not be a limitation, since the app is in its desing phase and anything can be changed.

The API is a PHP application getting HTTP requests. It talks to a PHP Database class that wraps all queries to the MariaDB database. All response from the aPI is given as JSON, dates formated as UTC strings. The data is shown via a web application. DateTime Javascript objects are responsible to convert the responses from the API to correct dates for the client time zone.

我有一个API的时区支持PHP MySQL。我的数据库是在UTC和日期时间的计算是在UTC时间转换为本地时间,只有当数据显示给用户。

问题是,当试图检索数据在一个不同的时区的用户自然的时间间隔,例如周、月、年等。DB列是UTC DateTime类型和存储日期。如果我需要的所有行分组每月只使用UTC日期存储在数据库中我会得到一些错误的行,如果带了一些列的差异转变月时间。

例如:行值2015-05-01 00:00:00是可能在UTC,但应该会在四月在负时间带任何用户。

所以使用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()应该有不同的输出一行取决于用户的时区。这是不可能使用柱sessions_date UTC如果想给用户一致的结果。

对的,现在我不知道用户的时区,但这不应该被限制,因为应用程序是在其设计阶段,什么都可以改变。

API是一个PHP应用程序获取HTTP请求。它与PHP的数据库类,封装了所有的数据库查询到MariaDB。所有的反应从API为JSON,日期格式字符串为UTC。通过Web应用程序显示数据。DateTime JavaScript对象负责将响应从API来纠正客户端时区的日期。

answer1: 回答1:

Firstly, the only reliable way you have of getting the client's timezone is through javascript, assuming their computer's time-settings are correct.

Solution One - Javascript to PHP

I suggest collecting the users timezone with the following javascript:

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

You will need to store this value in the database. You then format the UTC time with PHP code similar to this:

$date = new DateTime();
$timezone = new DateTimeZone('Australia/Sydney');

$date->setTimezone($timezone);
$formatted_date = $date->format('Y-m-d H:i:s');

Solution Two - PHP to Javascript

You convert a UTC string into a unix timestamp with the following:

$time = strtotime($utc);

You then return that to the browser, and have javascript format it like this:

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

Good luck!

edit: For converting MySQL for use with queries, use CONVERT_TZ, the format being:

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

For example:

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

首先,你必须得到客户的时区的唯一可靠的途径是通过JavaScript,假设他们的计算机时间设置正确。

一个JavaScript PHP解决方案

我建议用下面的JavaScript收集用户的时区:

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

您将需要将此值存储在数据库中。然后用PHP代码的格式类似于UTC时间:

$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时间戳:

$time = strtotime($utc);

然后返回到浏览器,并有JavaScript格式这样:

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

祝你好运!

编辑:用于查询、转换convert_tz MySQL使用,格式为:

convert_tz('date /时间字段,起步的时区,'ending时区”)

例如:

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