How to Convert UTC Date to Local Time Zone in MySQL Select Query?
- Barbara StreisandOriginal
- 2024-11-17 14:47:01612browse
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!