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

Question

I am using this Where  Condition in One Of my query with MySql Database My Problem is that i have one displaytime  column in my table but that table column shows the data in UTC Time and i want to convert that displaytime column in the Local Time Zone so how can i provide this facility from query itself   I have goggled the things and by that i knew that something like SELECT CONVERT TZ   will work for that but its not working for me   Here is my query in which i need to convert displaytime to local time zone   so can anyone please guide me   WHERE displaytime  gt    2012-12-01 00 00 00    AND displaytime  lt   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    SAmple DAta

User · Accepted Answer

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 gt  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

User · Answer

select convert tz now     session time zone   05 30     replace   05 30  with desired timezone  see here - https   stackoverflow com a 3984412 2359994  to format into desired time format  eg    select DATE FORMAT convert tz now     session time zone   05 30      b  d  Y  h  i  s  p      you will get similar to this -  Dec 17 2014 10 39 56 AM

User · Answer

In my case  where the timezones are not available on the server  this works great   SELECT CONVERT TZ  date field    00 00    global time zone  FROM  table    Note  global time zone uses the server timezone  You have to make sure  that it has the desired timezone

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

Examples related to mysql

Examples related to utc

Examples related to localtime

Examples related to convert-tz