Database timezone

问题:

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

回答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!!

回答2:

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

回答3:

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.