mysql 查询时区,MySQL时区查询

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

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

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 ?

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

Thanks

解决方案

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;

Therefore, if you've executed

SET NAMES time_zone = "+0:00";

then you will use UTC-based values.

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

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.

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