How do I get the current time zone of MySQL

Question

Anyone knows if there is such a function in MySQL   UPDATE  This doesn t output any valid info   mysql gt  SELECT   global time zone    session time zone   -------------------- ---------------------      global time zone     session time zone    -------------------- ---------------------    SYSTEM               SYSTEM                 -------------------- ---------------------    Or maybe MySQL itself can t know exactly the time zone used that s fine  we can involve PHP here  as long as I can get valid info not like SYSTEM

User · Answer

To get Current timezone of the mysql you can do following things:

 SELECT @@system_time_zone;   # from this you can get the system timezone 
 SELECT IF(@@session.time_zone = 'SYSTEM', @@system_time_zone, @@session.time_zone) # This will give you time zone if system timezone is different from global timezone

Now if you want to change the mysql timezone then:

 SET GLOBAL time_zone = '+00:00';   # this will set mysql timezone in UTC
 SET @@session.time_zone = "+00:00";  # by this you can chnage the timezone only for your particular session

User · Answer

Insert a dummy record into one of your databases that has a timestamp Select that record and get value of timestamp  Delete that record   Gets for sure the timezone that the server is using to write data and ignores PHP timezones

User · Answer

The command mention in the description returns  SYSTEM  which indicated it takes the timezone of the server  Which is not useful for our query   Following query will help to understand the timezone  SELECT TIMEDIFF NOW    UTC TIMESTAMP  as GMT TIME DIFF    Above query will give you the time interval with respect to Coordinated Universal Time UTC   So you can easily analyze the timezone  if the database time zone is IST the output will be 5 30   UTC TIMESTAMP  In MySQL  the UTC TIMESTAMP returns the current UTC date and time as a value in  YYYY-MM-DD HH MM SS  or YYYYMMDDHHMMSS uuuuuu format depending on the usage of the function i e  in a string or numeric context   NOW    NOW   function  MySQL NOW   returns the value of current date and time in  YYYY-MM-DD HH MM SS  format or YYYYMMDDHHMMSS uuuuuu format depending on the context  numeric or string  of the function  CURRENT TIMESTAMP  CURRENT TIMESTAMP    LOCALTIME  LOCALTIME    LOCALTIMESTAMP  LOCALTIMESTAMP   are synonyms of NOW

User · Answer

Use LPAD TIME FORMAT TIMEDIFF NOW    UTC TIMESTAMP      H  i     6        to get a value in MySQL s timezone format that you can conveniently use with CONVERT TZ    Note that the timezone offset you get is only valid at the moment in time where the expression is evaluated since the offset may change over time if you have daylight savings time  Yet the expression is useful together with NOW   to store the offset with the local time  which disambiguates what NOW   yields   In DST timezones  NOW   jumps back one hour once a year  thus has some duplicate values for distinct points in time

User · Answer

To anyone come to find timezone of mysql db   With this query you can get current timezone    mysql gt  SELECT   system time zone as tz   -------     tz      -------     CET     -------

User · Answer

You just need to restart mysqld after altering timezone of System    The Global time zone of MySQL takes timezone of System  When you change any such attribute of system  you just need a restart of Mysqld

User · Answer

If you need the GMT difference as an integer  SELECT EXTRACT HOUR FROM  TIMEDIFF NOW    UTC TIMESTAMP    AS  timezone

User · Answer

The query below returns the timezone of the current session   select timediff now   convert tz now     session time zone   00 00

User · Answer

It may be  select timediff current time   utc time      You won t get the timezone value directly this way     global time zone cannot be used as it is a variable  and it returns the value  SYSTEM    If you need to use your query in a session with a changed timezone by using  session SET TIME ZONE    then you will get that with   session time zone  If you query   global time zone  then you get  SYSTEM    If you try datediff  date sub  or timediff with now   and utc time    then you ll probably run into conversion issues   But the things suggested above will probably work at least with some server versions   My version is 5 5 43-37 and is a hosted solution

User · Answer

You can try the following    select sec to time TIME TO SEC  curtime      48000     Here you can specify your time difference as seconds

User · Answer

From the manual  section 9 6       The current values of the global and client-specific time zones can be retrieved like this    mysql gt  SELECT   global time zone    session time zone    Edit The above returns SYSTEM if MySQL is set to slave to the system s timezone  which is less than helpful  Since you re using PHP  if the answer from MySQL is SYSTEM  you can then ask the system what timezone it s using via date default timezone get   Of course  as VolkerK pointed out  PHP may be running on a different server  but as assumptions go  assuming the web server and the DB server it s talking to are set to  if not actually in  the same timezone isn t a huge leap   But beware that  as with MySQL   you can set the timezone that PHP uses  date default timezone set   which means it may report a different value than the OS is using  If you re in control of the PHP code  you should know whether you re doing that and be okay   But the whole question of what timezone the MySQL server is using may be a tangent  because asking the server what timezone it s in tells you absolutely nothing about the data in the database  Read on for details   Further discussion   If you re in control of the server  of course you can ensure that the timezone is a known quantity  If you re not in control of the server  you can set the timezone used by your connection like this   set time zone     00 00     That sets the timezone to GMT  so that any further operations  like now    will use GMT   Note  though  that time and date values are not stored with timezone information in MySQL   mysql gt  create table foo  tstamp datetime  Engine MyISAM  Query OK  0 rows affected  0 06 sec   mysql gt  insert into foo  tstamp  values  now     Query OK  1 row affected  0 00 sec   mysql gt  set time zone     01 00   Query OK  0 rows affected  0 00 sec   mysql gt  select tstamp from foo   ---------------------    tstamp                 ---------------------    2010-05-29 08 31 59    ---------------------  1 row in set  0 00 sec   mysql gt  set time zone     02 00   Query OK  0 rows affected  0 00 sec   mysql gt  select tstamp from foo   ---------------------    tstamp                 ---------------------    2010-05-29 08 31 59         lt    Note  no change   ---------------------  1 row in set  0 00 sec   mysql gt  select now     ---------------------    now                    ---------------------    2010-05-29 10 32 32    ---------------------  1 row in set  0 00 sec   mysql gt  set time zone     00 00   Query OK  0 rows affected  0 00 sec   mysql gt  select now     ---------------------    now                    ---------------------    2010-05-29 08 32 38         lt    Note  it changed   ---------------------  1 row in set  0 00 sec    So knowing the timezone of the server is only important in terms of functions that get the time right now  such as now    unix timestamp    etc   it doesn t tell you anything about what timezone the dates in the database data are using  You might choose to assume they were written using the server s timezone  but that assumption may well be flawed  To know the timezone of any dates or times stored in the data  you have to ensure that they re stored with timezone information or  as I do  ensure they re always in GMT   Why is assuming the data was written using the server s timezone flawed  Well  for one thing  the data may have been written using a connection that set a different timezone  The database may have been moved from one server to another  where the servers were in different timezones  I ran into that when I inherited a database that had moved from Texas to California   But even if the data is written on the server  with its current time zone  it s still ambiguous  Last year  in the United States  Daylight Savings Time was turned off at 2 00 a m  on November 1st  Suppose my server is in California using the Pacific timezone and I have the value 2009-11-01 01 30 00 in the database  When was it  Was that 1 30 a m  November 1st PDT  or 1 30 a m  November 1st PST  an hour later   You have absolutely no way of knowing  Moral  Always store dates times in GMT  which doesn t do DST  and convert to the desired timezone as when necessary

User · Answer

As Jakub Vr  na  The creator or Adminer and NotORM  mentions in the comments  to select the current timezone offset in TIME use   SELECT TIMEDIFF NOW    UTC TIMESTAMP     It will return  02 00 00 if your timezone is  2 00 for that date  I made a cheatsheet here  Should MySQL have its timezone set to UTC

User · Answer

Simply SELECT   system time zone   Returns PST  or whatever is relevant to your system    If you re trying to determine the session timezone you can use this query  SELECT IF   session time zone    SYSTEM     system time zone    session time zone    Which will return the session timezone if it differs from the system timezone

User · Answer

My PHP framework uses  SET LOCAL time zone  Whatever    on after connect  where  Whatever     date default timezone get    Not my solution  but this ensures SYSTEM timezone of MySQL server is always the same as PHP s one  So  yes  PHP is strongly envolved and can affect it

User · Answer

Check out MySQL Server Time Zone Support and the system time zone system variable   Does that help

User · Answer

To get the current time according to your timezone  you can use the following   in my case its   5 30       select DATE FORMAT convert tz now     session time zone   05 30      Y- m- d

User · Answer

Try using the following code     ASP CLASSIC Set dbdate   Server CreateObject  ADODB Recordset   dbdate ActiveConnection   MM connection dbdate Source    SELECT NOW   AS currentserverdate   dbdate Open   currentdate   dbdate Fields  currentserverdate   Value response Write  Server Time is   amp currentdate  dbdate Close   Set dbdate   Nothing

[mysql] How do I get the current time zone of MySQL?

Examples related to mysql

Examples related to timezone