英文:

sql default time zone set problem facing please solve

问题 {#heading}

1st ..

设置时区 = 'Asia/Kolkata'

2nd .

设置 `time_zone` = 'Asia/Kolkata'

3rd .

设置全局 time_zone = '+05:30'

4th.

设置全局 time_zone = "Asia/Kolkata"

但这不起作用，显示以下错误：

#1227 - 拒绝访问；您需要（至少之一）进行此操作的 SUPER 权限

英文:

im trying to change the current timezone to Asia/Kolkata by using the following query in phpmyadmin. But its not working..

1st ..

set time_zone = '+5:30'

2nd .

SET `time_zone` = 'Asia/Kolkata'

3rd .

SET GLOBAL time_zone = '+05:30';

4th.

SET GLOBAL time_zone = "Asia/Kolkata";

But this not working showings this error

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

答案1 {#1}

得分: 2

设置全局时区实际上需要在DBMS上具有管理员权限：它会影响到数据库的所有用户，并且如果更改时区可能会对其他软件造成混淆。您的错误消息清楚地表明您没有该权限。将DBMS的全局时区设置为UTC被视为一种良好的实践。

设置会话时区（您的前两个示例）不需要特权，因为它仅影响您的会话。许多应用程序每次连接到DBMS时都会设置时区，因为这允许当前的时区设置被处理为用户首选项。

您没有说您的前两个示例是否成功。如果 SET SESSION time_zone='Asia/Kolkata' 失败而 SET SESSION time_zone='+05:30' 成功，这意味着您的DBMS的时区表尚未由系统管理员加载。请那个人阅读此文档：链接。请注意，许多托管提供商拒绝加载这些表，尽管它们是MySQL和MariaDb的标准配置的一部分。

（如果您的前两个示例都失败了，那么可能存在其他问题。）

对于仅适用于印度的应用程序，不可用的时区表不是问题，因为印度不使用夏令时。但对于许多地方来说，本地时间与UTC之间的偏移每年变化两次。时区表实现了这些变化。例如，America/Halifax 在夏季切换为 -03:00，而在冬季切换为 -04:00。 英文:

Setting the global timezone does in fact require administrative privilege on the DBMS: it affects all the database's users, and could cause confusion to other software if it changes. Your error message makes it clear you don't have that privilege. It's considered a good practice to keep a DBMS's global timezone set to UTC.

Setting the session time zone (your first two examples) does not require privilege, as it affects only your session. Many applications set the time zone every time they connect to the DBMS, as this allows the current time zone setting to be handled as a user preference.

You didn't say whether your first two examples succeeded. If SET SESSION time_zone='Asia/Kolkata' fails and SET SESSION time_zone='+05:30' succeeds, it means your DBMS's time zone tables have not been loaded by your system administrator. Ask that person to read this. Be aware that many hosting providers refuse to load these tables, even though they're part of the standard configuration for MySQL and MariaDb.

(If both your first two examples failed, something else is wrong.)

For India-only apps, not having the time zone tables available isn't a problem because India does not use daylight time. But for many places, the offset between local time and UTC changes twice a year. The time zone tables implement those changes. For example, America/Halifax switches between -03:00 in summer and -04:00 in winter.