CODEWITHSUNDEEP
mysqldatedatetimetimetimezone
Klanto Aguntuk
Klanto Aguntuk

Reputation: 719

SELECT CONVERT_TZ command isn't affecting MySql server timezone

i'm on a shared unix server.

it has the default time zone -06:00:00

as per MySQL Manual i'm trying to set my timezone by executing the following command:

SELECT CONVERT_TZ('2004-01-01 12:00:00','-06:00','+07:00');

the command is being executed but it's not affecting the timezone. what's wrong with the server or my SQL command?

thanks,

Upvotes: 0

Views: 329

Answers (2)

D Mac
D Mac

Reputation: 3809

You're on a shared unix server, so you probably don't want to change the time zone of the MySQL server (unless you want to mess up everybody else who is using it).

Check the documentation for CONVERT_TZ - it doesn't set the time zone, it converts a datetime from one time zone to another. It returns a datetime.

For your example, SELECT CONVERT_TZ('2004-01-01 12:00:00','-06:00','+07:00'); correctly returns 2004-01-02 01:00:00.

If you change your connection's time zone, that won't affect anything for other users, but it will allow all your transactions on your connection to take place with a different default time zone. Use:

SET time_zone = {timezone}; -- where {timezone} is the timezone you want to use

And see http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html for the documentation

Upvotes: 1

Amit Vikram
Amit Vikram

Reputation: 402

if you want to check the mysql server timezone

use below SQl query 

SELECT @@global.time_zone, @@session.time_zone;

if you want to change the timezone you can simply try this 

set @@global.time_zone = '+07:00'

This will change the timezone of mysql server.

Note: the change will be flushed if you restart the server, in that case you should fire above query again.

Hope this helps.

Upvotes: 1

Related Questions