How to Convert UTC Date To Local time Zone in MySql Select Query

by

SELECT CONVERT_TZ() will work for that.but its not working for me.

Why, what error do you get?

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

should work if your column type is timestamp, or date

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz

Test how this works:

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

Check your timezone-table

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

http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

If those tables are empty, you have not initialized your timezone tables. According to link above you can use mysql_tzinfo_to_sql program to load the Time Zone Tables. Please try this

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo

or if not working read more: http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html

More Related Contents:

  1. Should MySQL have its timezone set to UTC?
  2. convert_tz returns null
  3. MySQL CONVERT_TZ()
  4. Can MySQL convert a stored UTC time to local timezone?
  5. SQL select only rows with max value on a column [duplicate]
  6. What is the error “Every derived table must have its own alias” in MySQL?
  7. Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)
  8. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  9. How to pivot a MySQL entity-attribute-value schema
  10. MySQL find_in_set with multiple search string
  11. Oracle Database Link – MySQL Equivalent?
  12. How to escape single quotes in MySQL
  13. MySQL comparison with null value
  14. Unique constraint that allows empty values in MySQL
  15. INTERSECT in MySQL
  16. MySQL select with CONCAT condition
  17. mysql field name from variable
  18. Multiple select statements in Single query
  19. How to get a list of MySQL views?
  20. MySQL SELECT LIKE or REGEXP to match multiple words in one record
  21. Syntax error when using except in a query
  22. Convert Unix timestamp into human readable date using MySQL
  23. Why the order of evaluation for expressions involving user variables is undefined?
  24. Easiest way to copy a table from one database to another?
  25. The best way to remove value from SET field?
  26. How do you force mysql LIKE to be case sensitive? [duplicate]
  27. MySQL Alter table causes Error: Invalid use of NULL value
  28. uninitialized constant MysqlCompat::MysqlRes (using mms2r gem)
  29. Migrating data into Firebase from MySQL
  30. Inventory management with stock options

Leave a Comment