How to Convert UTC Date to Local Time Zone in MySQL Select Query?

Converting UTC Date to Local Time Zone in MySQL Select Query

Problem

A MySQL query requires converting a "displaytime" column, which stores UTC timestamps, to the local time zone. However, using the CONVERT_TZ() function is not producing the expected results.

Answer

1. Verify Error Message

Check the error message to determine why CONVERT_TZ() is not working.

2. Ensure Compatible Column Types

CONVERT_TZ() requires the input column to be a timestamp or date data type. Check the data type of the "displaytime" column.

3. Use Correct Syntax

The correct syntax for CONVERT_TZ() is:

4. Example Usage

For instance, to convert displaytime from UTC to US Eastern Time (-05:00 GMT), use:

5. Check Time Zone Table

Ensure that the MySQL timezone tables are properly initialized. Run the following commands to check:

6. Load Time Zone Data

If the time zone tables are empty, use the mysql_tzinfo_to_sql program to load them:

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