為什麼 CONVERT_TZ() 在我的 MySQL 查詢中不起作用？

Converting UTC Dates to Local Time Zones in MySQL Queries

To convert UTC timestamps to local time zones in your MySQL queries, you can use the CONVERT_TZ() function. However, it's important to note that using this function may not always be successful.

Why CONVERT_TZ() Might Fail

If CONVERT_TZ() is not working for you, there are a few possible reasons:

• Incorrect Time Zone Specifications: Ensure that you have provided valid time zone identifiers in the CONVERT_TZ() function.
• Empty Time Zone Tables: MySQL relies on time zone tables to perform time zone conversions. If these tables are empty, you need to initialize them using the mysql_tzinfo_to_sql program.

Query Example Using CONVERT_TZ()

The following query converts a timestamp column called displaytime to the MET time zone:

SELECT CONVERT_TZ(displaytime, '+00:00', '+04:00');

Checking Time Zone Tables

To verify if your time zone tables are initialized, execute the following queries:

SELECT * FROM mysql.time_zone;
SELECT * FROM mysql.time_zone_name;

Initializing Time Zone Tables

If the time zone tables are empty, use the following command to load them:

mysql_tzinfo_to_sql /usr/share/zoneinfo

Once the time zone tables are initialized, try using CONVERT_TZ() again. If you still encounter issues, consult the MySQL documentation for more detailed troubleshooting steps:

• [Date and Time Functions: CONVERT_TZ()](https://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz)
• [Time Zone Support](https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html)
• [mysql_tzinfo_to_sql](https://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html)

以上是為什麼 CONVERT_TZ() 在我的 MySQL 查詢中不起作用？的詳細內容。更多資訊請關注PHP中文網其他相關文章！