查看MySQL服务器时区支持和system_time_zone系统变量。这有用吗?

参考手册(第9.6节):

全局时区和客户端特定时区的当前值可以像这样检索: mysql> SELECT @@global。time_zone @@session.time_zone;

Edit The above returns SYSTEM if MySQL is set to use the system's timezone, which is less than helpful. Since you're using PHP, if the answer from MySQL is SYSTEM, you can then ask the system what timezone it's using via date_default_timezone_get. (Of course, as VolkerK pointed out, PHP may be running on a different server, but as assumptions go, assuming the web server and the DB server it's talking to are set to [if not actually in] the same timezone isn't a huge leap.) But beware that (as with MySQL), you can set the timezone that PHP uses (date_default_timezone_set), which means it may report a different value than the OS is using. If you're in control of the PHP code, you should know whether you're doing that and be okay.

但是MySQL服务器使用的时区可能是一个切题的问题，因为询问服务器它在哪个时区完全不能告诉您数据库中的数据。阅读以下细节:

进一步讨论:

如果您可以控制服务器，当然可以确保时区是已知的。如果你不能控制服务器，你可以像这样设置你的连接所使用的时区:

set time_zone = '+00:00';

这将时区设置为GMT，因此任何进一步的操作(如now())都将使用GMT。

注意，在MySQL中，时间和日期值不存储在时区信息中:

mysql> create table foo (tstamp datetime) Engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into foo (tstamp) values (now());
Query OK, 1 row affected (0.00 sec)

mysql> set time_zone = '+01:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select tstamp from foo;
+---------------------+
| tstamp              |
+---------------------+
| 2010-05-29 08:31:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+02:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select tstamp from foo;
+---------------------+
| tstamp              |
+---------------------+
| 2010-05-29 08:31:59 |      <== Note, no change!
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-05-29 10:32:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-05-29 08:32:38 |      <== Note, it changed!
+---------------------+
1 row in set (0.00 sec)

So knowing the timezone of the server is only important in terms of functions that get the time right now, such as now(), unix_timestamp(), etc.; it doesn't tell you anything about what timezone the dates in the database data are using. You might choose to assume they were written using the server's timezone, but that assumption may well be flawed. To know the timezone of any dates or times stored in the data, you have to ensure that they're stored with timezone information or (as I do) ensure they're always in GMT.

Why is assuming the data was written using the server's timezone flawed? Well, for one thing, the data may have been written using a connection that set a different timezone. The database may have been moved from one server to another, where the servers were in different timezones (I ran into that when I inherited a database that had moved from Texas to California). But even if the data is written on the server, with its current time zone, it's still ambiguous. Last year, in the United States, Daylight Savings Time was turned off at 2:00 a.m. on November 1st. Suppose my server is in California using the Pacific timezone and I have the value 2009-11-01 01:30:00 in the database. When was it? Was that 1:30 a.m. November 1st PDT, or 1:30 a.m. November 1st PST (an hour later)? You have absolutely no way of knowing. Moral: Always store dates/times in GMT (which doesn't do DST) and convert to the desired timezone as/when necessary.

下面的查询返回当前会话的时区。

select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00'));

简单的 选择@@system_time_zone;

返回PST(或与您的系统相关的任何值)。

如果你试图确定会话时区，你可以使用这个查询: 选择如果(@@session。time_zone = 'SYSTEM'， @@system_time_zone， @@session.time_zone);

如果会话时区与系统时区不同，将返回会话时区。

正如Jakub Vrána(创建者或管理员和NotORM)在评论中提到的，在TIME中选择当前时区偏移量使用:

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

如果该日期的时区是+2:00，它将返回:02:00:00

我在这里做了一个备忘单:MySQL是否应该将它的时区设置为UTC?

你只需要在修改系统时区后重新启动mysqld ..

MySQL的Global timezone取System的timezone。当你改变任何这样的系统属性，你只需要重新启动Mysqld。

如果需要将GMT差值作为整数:

SELECT EXTRACT(HOUR FROM (TIMEDIFF(NOW(), UTC_TIMESTAMP))) AS `timezone`

将一个虚拟记录插入到具有时间戳的数据库中 选择该记录并获取时间戳的值。 删除该记录。获取服务器写入数据时使用的时区，忽略PHP时区。

可能是这样

select timediff(current_time(),utc_time())

通过这种方式，您不会直接获得时区值。

@@global。time_zone不能被使用，因为它是一个变量，它返回的值是'SYSTEM'。

如果您需要使用session SET TIME_ZONE =在更改了时区的会话中使用您的查询，那么您将使用@@session.time_zone获得该查询。如果查询@@global。time_zone，然后你得到'SYSTEM'。

如果使用now()和utc_time()尝试datediff、date_sub或timediff，那么可能会遇到转换问题。

但是上面建议的东西至少可以在某些服务器版本上使用。我的版本是5.5.43-37，是一个托管解决方案。

我的PHP框架使用

SET LOCAL time_zone='Whatever'

在connect之后，where 'Whatever' == date_default_timezone_get()

这不是我的解决方案，但这可以确保MySQL服务器的系统时区始终与PHP的时区相同

因此，是的，PHP强烈地参与其中，并可以影响它

要获取mysql的当前时区，你可以做以下事情:

 SELECT @@system_time_zone;   # from this you can get the system timezone 
 SELECT IF(@@session.time_zone = 'SYSTEM', @@system_time_zone, @@session.time_zone) # This will give you time zone if system timezone is different from global timezone

现在如果你想改变mysql时区，那么:

 SET GLOBAL time_zone = '+00:00';   # this will set mysql timezone in UTC
 SET @@session.time_zone = "+00:00";  # by this you can chnage the timezone only for your particular session 

你可以尝试以下方法:

select sec_to_time(TIME_TO_SEC( curtime()) + 48000);

在这里，您可以将时差指定为秒

Use LPAD(TIME_FORMAT(TIMEDIFF(NOW(), UTC_TIMESTAMP),’%H:%i’),6,’+') to get a value in MySQL's timezone format that you can conveniently use with CONVERT_TZ(). Note that the timezone offset you get is only valid at the moment in time where the expression is evaluated since the offset may change over time if you have daylight savings time. Yet the expression is useful together with NOW() to store the offset with the local time, which disambiguates what NOW() yields. (In DST timezones, NOW() jumps back one hour once a year, thus has some duplicate values for distinct points in time).

要根据您的时区获取当前时间，您可以使用以下命令(在我的示例中是'+5:30')

选择DATE_FORMAT (convert_tz(现在(),@@session.time_zone, ' + 05:30 '), Y % - % - % d ')

尝试使用以下代码:

//ASP CLASSIC
Set dbdate = Server.CreateObject("ADODB.Recordset")
dbdate.ActiveConnection = MM_connection
dbdate.Source = "SELECT NOW() AS currentserverdate "
dbdate.Open()
currentdate = dbdate.Fields("currentserverdate").Value
response.Write("Server Time is "&currentdate)
dbdate.Close()
Set dbdate = Nothing

描述中提到的命令返回“SYSTEM”，表示它采用服务器的时区。这对我们的查询没有用处。

下面的查询将有助于了解时区

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) as GMT_TIME_DIFF;

以上查询将提供与协调世界时(UTC)相关的时间间隔。所以你可以很容易地分析时区。如果数据库时区是IST，则输出将是5:30

UTC_TIMESTAMP

在MySQL中，UTC_TIMESTAMP返回当前UTC日期和时间，作为'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS的值。Uuuuuu格式取决于函数的用法，即在字符串或数字上下文中。

现在()

现在()函数。MySQL NOW()以'YYYY-MM-DD HH:MM:SS'格式或YYYYMMDDHHMMSS返回当前日期和时间的值。Uuuuuu格式，取决于函数的上下文(数字或字符串)。CURRENT_TIMESTAMP, CURRENT_TIMESTAMP()， LOCALTIME, LOCALTIME()， LOCALTIMESTAMP, LOCALTIMESTAMP()是NOW()的同义词。

任何人来查找mysql数据库的时区。

通过这个查询，你可以得到当前的时区:

mysql> SELECT @@system_time_zone as tz;
+-------+
|  tz   |
+-------+
|  CET  |
+-------+

这不是对这个问题的直接回答，但这篇博客文章提供了关于这个主题的有价值的信息:

https://danuka-praneeth.medium.com/guide-to-time-zones-conversion-between-zones-and-storing-in-mysql-da4fc4350cd9。

引用博客文章:

在MySQL5+中，TIMESTAMP值是从会话时区转换而来的 存储从UTC到会话时区 检索。但是DATETIME不做任何转换。