MySQL时区查询 [英] MySQL Timezone Query

问题描述

我有两个字段:time_scan_start和time_scan_end(时间戳字段).

I got two fields : time_scan_start and time_scan_end (timestamp field).

当我使用PDO打开与MySQL的连接时，我会使用SET NAMES utf8,time_zone = "+0:00";.

When I open my connection to MySQL with PDO, I use SET NAMES utf8,time_zone = "+0:00";.

现在，当我在MySQL中进行查询时，是否必须使用UTC时间WHERE time_scan_start >= UTC还是需要使用PHP本地区域? MySQL会自行进行调整吗?

Now, when I do a query in MySQL, do I have to use UTC time WHERE time_scan_start >= UTC or I need to use the PHP local zone ? Will MySQL do the ajustements it self ?

我用gmdate和date做过一些测试，有时它可以工作，而其他则不起作用.

I did some test with gmdate and date, sometime it work, other it don't.

谢谢

推荐答案

如果要与TIMESTAMP字段进行比较，则需要在服务器的时区中使用比较值.您可以通过以下方式确定服务器时区:

If you are comparing against TIMESTAMP fields, you need to use comparison values in the timezone of the server. You can determine the server timezone via:

SELECT @@time_zone;

因此，如果您已执行

SET NAMES time_zone = "+0:00";

然后，您将使用基于UTC的值.

then you will use UTC-based values.

这是因为TIMESTAMP字段以UTC格式存储在MySQL中，并在显示(或比较)之前转换为服务器的时区.

This is because TIMESTAMP fields are stored in MySQL in UTC, and are converted to the server's timezone before display (or a comparison).

注意:如果要与DATETIME字段或TIME字段进行比较，则需要在与将值插入字段时所用的相同时区中使用比较值.

Note: if you are comparing against DATETIME fields or TIME fields, you will need to use a comparision value in the same timezone as was used when the value was inserted into the field.

这是因为DATETIME和TIME字段存储在MySQL中，没有任何时区信息，并且在显示(或比较)之前不会进行转换.

This is because DATETIME and TIME fields are stored in MySQL without any timezone information, and are not converted before display (or a comparison).

这篇关于MySQL时区查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！