MySQL Timezone Query

Question

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

Answer 1

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).

Answer 2

MySQL DATETIME knows nothing of timezone. So you need to decide which timezone will be your "default" (usually UTC) and store all your timestamps using that timezone, using CONVERT_TZ if necessary.

Then, do the same for the search, i.e. convert the values to UTC before using them in the search.