我期待着提供简单的日志，我的用户通过我的api从不同的时区，我有点迷路，希望能得到一些帮助。
我有一个名为logs的下表：
CREATE TABLE `logs` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`ip` VARCHAR(45) NOT NULL,
`timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;我有一个小时表可以在24小时内绑定我的查询。
CREATE TABLE `log_hours` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`hourId` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO log_hours
(hourId)
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12),
(13),
(14),
(15),
(16),
(17),
(18),
(19),
(20),
(21),
(22),
(23),
(24);每次我得到一个api命中我存储日志采取以下数据。
id | userId | ip | timestamp
1 1 0.0.0.0 2018-08-23 14:20:34
2 1 0.0.0.0 2018-08-23 14:20:34
3 1 0.0.0.0 2018-08-23 14:20:34
4 1 0.0.0.0 2018-08-23 14:20:34
5 1 0.0.0.0 2018-08-23 14:20:34现在，如果我像下面这样运行我的查询。
SELECT DW.hourId AS hour, ifnull(t.hits,0) hits from log_hours DW left join (
SELECT COUNT( * ) AS hits, HOUR( logs.timestamp ) AS `hour`
FROM logs WHERE DAY( logs.timestamp ) = DAY(CURDATE())
AND MONTH( logs.timestamp ) = MONTH(CURDATE())
AND `userId` = 1 GROUP BY HOUR( logs.timestamp )) t on DW.hourId = t.hour
ORDER BY hour ASC这工作很好，给我的日志，每小时像这样。
现在我被困在时区我的数据库时区设置为英国夏令时。
比如说，我在美国有一个用户，如果他们在自己的时区点击了api，mysql会自动将他们的时区转换成我的bst时区，这样输出的数据才有意义吗？
我应该使用datetime not null default current\u timestamp还是应该使用int并使用php time（）函数插入记录？
我是否应该首先通过php从我的时区返回数据，然后转换成他们的时区？在这种情况下，什么是最佳实践？
编辑：
首先很抱歉，如果我问了一些愚蠢的问题，但我真的想完全理解这个过程。
在什么时候检索用户的本地时区？我应该把它作为必需的参数添加到api中。
这是我的php代码，我只是把所有的东西都添加到一个函数中，这样更容易阅读我的进程是mvc。
public function usage_get(){
$userId = $this->get('userId'); // i am obviously not getting the users info this way this is just for the example
// DO I MAKE THEM SEND THEIR TIMEZONE TO THE API AS A PARAM THEN USE IT IN MYSQL
//https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-tz?
$timezone = $this->get('timezone');
// DO I SOMEHOW RETRIEVE THE TIME ZONE BASE ON IP AND GET IT THAT WAY?
$query = $this->db->query("SELECT DW.hourId AS hour, ifnull(t.hits,0) hits
from log_hours DW
left join (
SELECT COUNT( * ) AS hits, HOUR( logs.timestamp ) AS `hour`
FROM logs
WHERE DAY( logs.timestamp ) = DAY(CURDATE())
AND MONTH( logs.timestamp ) = MONTH(CURDATE())
AND `userId` = ".$this->db->escape_str($userId)."
GROUP BY HOUR( logs.timestamp )
) t on DW.hourId = t.hour ORDER BY hour ASC");
// DO I RUN A PHP FUNCTION AFTER THE MYSQL BASED ON TIMEZONE?
if($query->num_rows() > 0){
$this->response([
'status' => TRUE,
'message' => 'Success',
'data' => $query->result(),
], REST_Controller::HTTP_OK);
}else{
$this->response([
'status' => FALSE,
'message' => 'Error'
], REST_Controller::HTTP_OK);
}
}
3条答案
按热度按时间gzjq41n41#
保持简单永远是最好的主意。独立于本地时间保存所有日期。在我看来，其他一切都是不好的做法，因为当地时间只会导致后来的混乱和问题。
比如说，我在美国有一个用户，如果他们在自己的时区点击了api，mysql会自动将他们的时区转换成我的bst时区，这样输出的数据才有意义吗？
如果要将日志时间传递为本地时间，请将其转换为本地时间。在数据库中，应始终存储utc（+/-0）。在mysql中，您可以使用
CONVERT_TZ功能(https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-在查询过程中。我应该使用datetime not null default current\u timestamp还是应该使用int并使用php time（）函数插入记录？
CURRENT_TIMESTAMP这似乎是正确的方法，因为它是独立的（mysql是否应该将其时区设置为utc？）。我看不出有什么好的理由使用phptime()功能因为CURRENT_TIMESTAMP如果服务器时间设置正确的话，它是最精确的，而且总是正确的，但是基本上除了从php调用和实际的数据库插入之间的毫秒数之外，应该没有什么区别。我是否应该首先通过php从我的时区返回数据，然后转换成他们的时区？在这种情况下，什么是最佳实践？
在我看来，使用数据库进行转换是最好的（最快的）-至少如果您可以编写自己的sql查询。否则，通过php进行转换是完全合法的，但是会有一些最小的开销。如何做到这一点已经被问过：转换utc日期当地时间在php
在什么时候检索用户的本地时区？我应该把它作为必需的参数添加到api中吗？
这是一个应用程序架构的问题，没有“规则”。检索用户时区的可能性：
geoip：使用geoip和基于位置的匹配将ip与区域匹配。与许多解决方案一样，这里的缺点是，如果用户使用vpn或代理，它将显示vpn或代理ip的区域，而不是用户背后的物理ip。
javascript:javascript是客户端的，虽然很像geoip，但是如果客户端在vpn或代理之后，同样的问题仍然存在。您将不会显示用户的时区，而是显示vpn或代理的时区（以javascript获取客户端的时区）。
询问用户：时区以某种方式作为参数传递。
对于一个api，我会明确地推荐后者——要求时区作为参数，或者简单地将时间utc（+-0）传递给它们。也许正在使用你的api的用户有一个他们设置时区的配置文件？
如果不是，请考虑向他们传递一个结果，其中包含utc（+-0）时间以及带有猜测时区短码的本地时间。任何实现都是有效的，只需明确结果是哪个时区，以及用户如何正确查询即可。
vi4fp9gy2#
如果你把所有的时间戳都记录在世界时间里，你的头痛就会少一些。追踪谁有哪一个夏时制的地方变老很快。
p3rjfoxz3#
最后的想法（我的问题解决方案是）：
好的，首先感谢blackam提供的信息，我也一直在读这篇文章。
http://www.vertabelo.com/blog/technical-articles/the-proper-way-to-handle-multiple-time-zones-in-mysql
据我所知，utc是24小时时钟起始点的参考，然后可以将其偏移以转换为正确的时区这与以前的gmt或gmt相同不确定，但它是一个起始点。
当最初的时间基准必须被定义时，人们同意使它相对于地球上的某一点。格林威治是伦敦的一个自治市，是本初子午线（0度经度）的所在地。
然后用函数来表示偏移量，不管是在php还是mysql中，这些函数都是基于字符串参数来计算偏移量的，例如，这些参数可以更改并存储在全局数据库中。
mysql数据库：
PHP：
在模糊地理解了这一点后，我现在可以看到一个解决方案，为我的设置，因为我将有来自不同时区的多个用户注册我的服务，这对我来说是有意义的，在他们的帐户配置文件下有一个选项，以设置其正确的时区。
这样我就可以抓取他们的时区，然后更新mysql查询以返回他们时区中的正确数据我同意blackam我更喜欢使用mysql。
我的过程如下。
mysql数据库存储时间或设置为utc（仍在寻找正确的方法）
更新：
我想我已经完成了以下命令登录到mysql并在命令行运行。
SELECT @@global.time_zone, @@session.time_zone;然后返回你的时区我的。
然后我跑了。
现在我明白了。
用户通过他们的帐户设置时区，就像你在博客平台（比如wordpress）中设置时区一样。
时区存储在数据库中，然后通过api检索以进行转换。
比如说这个。
我现在从convert_z得到空值，所以也只是想找出解决这个问题的最佳方法，显然你需要将zoneinfo加载到mysql中。
这解决了我的时区问题：
这意味着我现在可以跑了。
输出：
您可以从这里获得时区：http://php.net/manual/en/timezones.php
到了那里请让我知道如果我把这一切都弄错了。