How to Convert UTC Dates to Local Time Zones in MySQL Select Queries?

Converting UTC Dates to Local Time Zones in MySQL Select Queries

Question:

How can I modify a MySQL query to convert a UTC timestamp column, "displaytime," into the local time zone?

Answer 1:

Use the CONVERT_TZ() function as follows:

SELECT CONVERT_TZ(displaytime, 'GMT', 'MET');

Answer 2:

Ensure that your timezone tables are initialized. Use the mysql_tzinfo_to_sql program to populate them:

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo

Once initialized, you can use the CONVERT_TZ() function as described above.

Additional Notes:

• Check your timezone-related tables using the following queries:

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

• If they are empty, you need to initialize them as described above.
• Ensure that your column type is either timestamp or date for the CONVERT_TZ() function to work correctly.
• Specify the time zones in the CONVERT_TZ() function as either HH:MM (for positive offsets from UTC) or -HH:MM (for negative offsets). For example, 00:00 for UTC and 04:00 for Moscow Standard Time.

The above is the detailed content of How to Convert UTC Dates to Local Time Zones in MySQL Select Queries?. For more information, please follow other related articles on the PHP Chinese website!