I have a MySQL database with a lot of data stored with timestamps all in UTC time.
我有一个MySQL数据库，其中有很多数据存储在UTC时间的时间戳中。
When a user queries that database, I want them to be able to view the data but in their local timezone.
当用户查询该数据库时，我希望他们能够在本地时区查看数据。
I'd prefer not to have to calculate an offset each time and change the timestamp with PHP code. Is there any other way?
我不希望每次都需要计算偏移量，并使用PHP代码更改时间戳。还有别的办法吗?
3 个解决方案
#1
2
You can use localtime() function to get the local time and find the matching records from database.
可以使用localtime()函数获取本地时间，并从数据库中查找匹配记录。
#2
1
It is possible to set a timezone per connection with mysql like so:
可以为每个连接mysql设置一个时区，如下所示:
SET time_zone = 'America/Los_Angeles';
or
或
SET time_zone = '-8:00';
But for me best solution would be localtime();
但是对我来说，最好的解决方案是localtime();
#3
0
Where I work we support clients worldwide and have found it easier to even set the server's system time to UTC. This allows us to ensure consistent logging and base service timestamps, and if we need to add servers to a pool in a different timezone, we don't have to worry about if localtime is set correctly (b/c it's already UTC).
在我工作的地方，我们在全球范围内支持客户端，并且发现更容易将服务器的系统时间设置为UTC。这允许我们确保一致的日志记录和基本的服务时间戳，并且如果我们需要将服务器添加到不同时区的池中，我们不必担心是否正确设置了localtime (b/c已经是UTC)。
However there is a cost, as the OP mentioned, our application has to calculate the timezones. We assign a timezone to our clients, and a user signing into a client site gets the client's timezone, so we have to calculate date/timestamps for presentation, every time. We do profile our apps on occasion and these timezone conversions barely register.
但是，正如OP所提到的，我们的应用程序必须计算时区，这是有成本的。我们为客户端分配了一个时区，登录客户端站点的用户将获得客户端时区，因此每次都必须计算日期/时间戳。我们偶尔会对我们的应用进行配置，而这些时区的转换几乎不存在。
At the end of the day it's really up to you and your situation when dealing with timezone conversions.
在一天结束的时候，你和你的情况在处理时区转换的时候是非常重要的。
#1
2
You can use localtime() function to get the local time and find the matching records from database.
可以使用localtime()函数获取本地时间，并从数据库中查找匹配记录。
#2
1
It is possible to set a timezone per connection with mysql like so:
可以为每个连接mysql设置一个时区，如下所示:
SET time_zone = 'America/Los_Angeles';
or
或
SET time_zone = '-8:00';
But for me best solution would be localtime();
但是对我来说，最好的解决方案是localtime();
#3
0
Where I work we support clients worldwide and have found it easier to even set the server's system time to UTC. This allows us to ensure consistent logging and base service timestamps, and if we need to add servers to a pool in a different timezone, we don't have to worry about if localtime is set correctly (b/c it's already UTC).
在我工作的地方，我们在全球范围内支持客户端，并且发现更容易将服务器的系统时间设置为UTC。这允许我们确保一致的日志记录和基本的服务时间戳，并且如果我们需要将服务器添加到不同时区的池中，我们不必担心是否正确设置了localtime (b/c已经是UTC)。
However there is a cost, as the OP mentioned, our application has to calculate the timezones. We assign a timezone to our clients, and a user signing into a client site gets the client's timezone, so we have to calculate date/timestamps for presentation, every time. We do profile our apps on occasion and these timezone conversions barely register.
但是，正如OP所提到的，我们的应用程序必须计算时区，这是有成本的。我们为客户端分配了一个时区，登录客户端站点的用户将获得客户端时区，因此每次都必须计算日期/时间戳。我们偶尔会对我们的应用进行配置，而这些时区的转换几乎不存在。
At the end of the day it's really up to you and your situation when dealing with timezone conversions.
在一天结束的时候，你和你的情况在处理时区转换的时候是非常重要的。