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

Converting UTC Dates to Local Time Zone in MySQL Select Query

Issue

You are working with a MySQL query that contains a "displaytime" column that stores timestamps in UTC. However, you need to display the timestamps in the local time zone. The CONVERT_TZ() function is not working as expected.

Solution

To convert a UTC timestamp to a local time zone using the CONVERT_TZ() function, you must specify the time zone abbreviations for both the UTC and local time zones. The correct syntax is as follows:

SELECT CONVERT_TZ(displaytime, 'UTC', 'LOCAL')

Example Query

WHERE displaytime >= '2012-12-01 00:00:00'
AND displaytime <= '2013-02-22 23:59:59'
AND ct.organizationId IN (
  SELECT t.organizationId
  FROM organization_ AS t
  JOIN organization_ AS p ON t.treePath LIKE CONCAT(p.treePath, '%')
  WHERE p.organizationId = 10707
)

Modified Query:

WHERE CONVERT_TZ(displaytime, 'UTC', 'LOCAL') >= '2012-12-01 00:00:00'
AND CONVERT_TZ(displaytime, 'UTC', 'LOCAL') <= '2013-02-22 23:59:59'
AND ct.organizationId IN (
  SELECT t.organizationId
  FROM organization_ AS t
  JOIN organization_ AS p ON t.treePath LIKE CONCAT(p.treePath, '%')
  WHERE p.organizationId = 10707
)

Troubleshooting

If CONVERT_TZ() is not working, it could be due to missing or empty time zone tables. You can initialize the time zone tables using the mysql_tzinfo_to_sql program.

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