1

I want to set different timezone for different database on a single cpanel(Phpmyadmin).I don't know how to set different timezone for different database. Please help me .. it is possible or not,

Thanks in advance...

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
samsad
  • 1,241
  • 1
  • 10
  • 15
  • 1
    It's not possible to change the timezone of a single databse on an instance, but as Madan has pointed below, you can control this for a `session` You can also refer to discussion at http://stackoverflow.com/questions/13916747/set-mysql-database-timezone-to-gmt – Incognito Feb 13 '13 at 06:29
  • Are you looking to have different timezone for different database on same server or different server ? – Minesh Feb 13 '13 at 06:33
  • Hi Manish..I am looking for different timezone for different database on same server. – samsad Feb 13 '13 at 07:12
  • @samsad is your question solved? – Mr. Radical Mar 11 '13 at 18:34

3 Answers3

1

If you are using PHP / MYSQL. Place the following code at top of the page or index page.

date_default_timezone_set('timezone_name'); example America/New_York

Only MySQL (works only for current session)

mysql> SET time_zone = 'timezone_name';

For global change

mysql> SET GLOBAL time_zone = 'timezone_name';

Hope this helps. Thanks!!

Madan Sapkota
  • 25,047
  • 11
  • 113
  • 117
  • Hi Madan... I am talking about database(MYSQL)...I don't want to set timezone through the PHP Code. – samsad Feb 13 '13 at 06:21
0

I agree with the comments and answer above that you cannot change the time settings per database on the same MYSQL server.

In addition to the answer from Madan Sapkota, I would like to add the following.

At the start of a query with time/ date values you start with SET TIME_ZONE = ""; And then continue with your normal query.

Another way to query your database is to use the convert_tz() function.

CONVERT_TZ(column_name,'+00:00','-05:00')
CONVERT_TZ(column_name,'UTC','America/New_York')

The first number / name is the current time zone the second is the desired time zone. To check which timezone your database is using use SELECT @@global.time_zone, @@session.time_zone; in your command line.

Further reading: How does one deal with multiple TimeZones in applications that store dates and times?, How to correctly set mysql timezone

Community
  • 1
  • 1
Mr. Radical
  • 1,847
  • 1
  • 19
  • 29
0

I had the same problem when capturing the CURRENT_TIMESTAMP.

I solved it the following way:

In your database class have a class with the this query SET @@session.time_zone = '+02:00';, in your construct(make sure you put you timezone). This will have to execute everytime you query the database.

I hope this helps.

Sboniso Marcus Nzimande
  • 694
  • 3
  • 12
  • 25