极客教程SQL CURRENT_TIMEZONE_ID()函数
SQL的 CURRENT_TIMEZONE_ID() 函数是一个内置函数，用于检索服务器或实例所观察到的当前时区的ID。
CURRENT_TIMEZONE_ID()函数返回的ID可能在不同的数据库管理系统上有所不同，可能是时区的名称或缩写。例如，在 Oracle 中，此函数可能返回美国东部时间的’America/New_York’，而在 IBM Db2 中，它可能返回相同时区的’EST5EDT’。
注意 - 在SQL中，时区始终设置为UTC，CURRENT_TIMEZONE_ID将返回UTC时区的ID。
语法
SQL CURRENT_TIMEZONE_ID() 函数的语法如下：
CURRENT_TIMEZONE_ID()
参数
此函数不接受任何参数。
示例
以下示例演示了在SQL中使用SQL CURRENT_TIMEZONE_ID()函数的用法：
SQL> SELECT CURRENT_TIMEZONE_ID() AS CURRENT_TIMEZONE_ID;
输出
当我们执行上述查询时，输出结果如下所示：
+----------------------+
| CURRENT_TIMEZONE_ID |
+----------------------+
| India Standard Time |
+----------------------+
示例
我们可以使用以下查询从数据库服务器检索所有的时区ID：
SQL> SELECT * FROM sys.time_zone_info;
输出
如果执行以上查询，结果如下所示：
+-----------------------------------+--------------------+------------------+
| name | current_utc_offset | is_currently_dst |
+-----------------------------------+--------------------+------------------+
| Dateline Standard Time | -12:00 | 0 |
| Dateline Standard Time | -11:00 | 0 |
| UTC-11 | -10:00 | 0 |
| Aleutian Standard Time | -10:00 | 0 |
| Hawaiian Standard Time | -09:30 | 0 |
| Marquesas Standard Time | -09:00 | 0 |
| Alaskan Standard Time | -09:00 | 0 |
| UTC-09 | -08:00 | 0 |
| Pacific Standard Time (Mexico) | -08:00 | 0 |
| UTC-08 | -08:00 | 0 |
| Pacific Standard Time | -07:00 | 0 |
| US Mountain Standard Time | -07:00 | 0 |
| Mountain Standard Time (Mexico) | -07:00 | 0 |
| Mountain Standard Time | -07:00 | 0 |
| Yukon Standard Time | -06:00 | 0 |
| Central America Standard Time | -06:00 | 0 |
| Central Standard Time | -05:00 | 0 |
| Easter Island Standard Time | -06:00 | 1 |
| Central Standard Time (Mexico) | -06:00 | 0 |
| Canada Central Standard Time | -05:00 | 0 |
| SA Pacific Standard Time | -05:00 | 0 |
| Eastern Standard Time (Mexico) | -05:00 | 0 |
| Eastern Standard Time | -05:00 | 0 |
| Haiti Standard Time | -05:00 | 0 |
| Cuba Standard Time | -05:00 | 0 |
| US Eastern Standard Time | -05:00 | 0 |
| Turks And Caicos Standard Time | -03:00 | 0 |
| Paraguay Standard Time | -04:00 | 1 |
| Atlantic Standard Time | -04:00 | 0 |
| Venezuela Standard Time | -04:00 | 0 |
| Central Brazilian Standard Time | -04:00 | 0 |
| SA Western Standard Time | -03:00 | 0 |
| Pacific SA Standard Time | -03:30 | 1 |
| Newfoundland Standard Time | -03:00 | 0 |
| Tocantins Standard Time | -03:00 | 0 |
| E. South America Standard Time | -03:00 | 0 |
| SA Eastern Standard Time | -03:00 | 0 |
| Argentina Standard Time | -03:00 | 0 |
| Greenland Standard Time | -03:00 | 0 |
| Montevideo Standard Time | -03:00 | 0 |
| Magallanes Standard Time | -03:00 | 0 |
| Saint Pierre Standard Time | -03:00 | 0 |
| Bahia Standard Time | -02:00 | 0 |
| UTC-02 | -02:00 | 0 |
| Mid-Atlantic Standard Time | -01:00 | 0 |
| Azores Standard Time | -01:00 | 0 |
| Cape Verde Standard Time | +00:00 | 0 |
| UTC | +00:00 | 0 |
| GMT Standard Time | +00:00 | 0 |
| Greenwich Standard Time | +00:00 | 0 |
| Sao Tome Standard Time | +01:00 | 0 |
| Morocco Standard Time | +01:00 | 1 |
| W. Europe Standard Time | +01:00 | 0 |
| Central Europe Standard Time | +01:00 | 0 |
| Romance Standard Time | +01:00 | 0 |
| Central European Standard Time | +01:00 | 0 |
| W. Central Africa Standard Time | +02:00 | 0 |
| GTB Standard Time | +02:00 | 0 |
| Middle East Standard Time | +02:00 | 0 |
| Egypt Standard Time | +02:00 | 0 |
| E. Europe Standard Time | +02:00 | 0 |
| Syria Standard Time | +02:00 | 0 |
| West Bank Standard Time | +02:00 | 0 |
| South Africa Standard Time | +02:00 | 0 |
| FLE Standard Time | +02:00 | 0 |
| Israel Standard Time | +02:00 | 0 |
| South Sudan Standard Time | +02:00 | 0 |
| Kaliningrad Standard Time | +02:00 | 0 |
| Sudan Standard Time | +02:00 | 0 |
| Libya Standard Time | +02:00 | 0 |
| Namibia Standard Time | +03:00 | 0 |
| Jordan Standard Time | +03:00 | 0 |
| Arabic Standard Time | +03:00 | 0 |
| Turkey Standard Time | +03:00 | 0 |
| Arab Standard Time | +03:00 | 0 |
| Belarus Standard Time | +03:00 | 0 |
| Russian Standard Time | +03:00 | 0 |
| E. Africa Standard Time | +03:00 | 0 |
| Volgograd Standard Time | +03:30 | 0 |
| Iran Standard Time | +04:00 | 0 |
| Arabian Standard Time | +04:00 | 0 |
| Astrakhan Standard Time | +04:00 | 0 |
| Azerbaijan Standard Time | +04:00 | 0 |
| Russia Time Zone 3 | +04:00 | 0 |
| Mauritius Standard Time | +04:00 | 0 |
| Saratov Standard Time | +04:00 | 0 |
| Georgian Standard Time | +04:00 | 0 |
| Caucasus Standard Time | +04:30 | 0 |
| Afghanistan Standard Time | +05:00 | 0 |
| West Asia Standard Time | +05:00 | 0 |
| Ekaterinburg Standard Time | +05:00 | 0 |
| Pakistan Standard Time | +05:00 | 0 |
| Qyzylorda Standard Time | +05:30 | 0 |
| India Standard Time | +05:30 | 0 |
| Sri Lanka Standard Time | +05:45 | 0 |
| Nepal Standard Time | +06:00 | 0 |
| Central Asia Standard Time | +06:00 | 0 |
| Bangladesh Standard Time | +06:00 | 0 |
| Omsk Standard Time | +06:30 | 0 |
| Myanmar Standard Time | +07:00 | 0 |
| SE Asia Standard Time | +07:00 | 0 |
| Altai Standard Time | +07:00 | 0 |
| W. Mongolia Standard Time | +07:00 | 0 |
| North Asia Standard Time | +07:00 | 0 |
| N. Central Asia Standard Time | +07:00 | 0 |
| Tomsk Standard Time | +08:00 | 0 |
| China Standard Time | +08:00 | 0 |
| North Asia East Standard Time | +08:00 | 0 |
| Singapore Standard Time | +08:00 | 0 |
| W. Australia Standard Time | +08:00 | 0 |
| Taipei Standard Time | +08:00 | 0 |
| Ulaanbaatar Standard Time | +08:45 | 0 |
| Aus Central W. Standard Time | +09:00 | 0 |
| Transbaikal Standard Time | +09:00 | 0 |
| Tokyo Standard Time | +09:00 | 0 |
| North Korea Standard Time | +09:00 | 0 |
| Korea Standard Time | +09:00 | 0 |
| Yakutsk Standard Time | +10:30 | 0 |
| Cen. Australia Standard Time | +09:30 | 1 |
| AUS Central Standard Time | +10:00 | 0 |
| E. Australia Standard Time | +11:00 | 0 |
| AUS Eastern Standard Time | +10:00 | 1 |
| West Pacific Standard Time | +11:00 | 0 |
| Tasmania Standard Time | +10:00 | 1 |
| Vladivostok Standard Time | +11:00 | 0 |
| Lord Howe Standard Time | +11:00 | 1 |
| Bougainville Standard Time | +11:00 | 0 |
| Russia Time Zone 10 | +11:00 | 0 |
| Magadan Standard Time | +12:00 | 0 |
| Norfolk Standard Time | +11:00 | 1 |
| Sakhalin Standard Time | +11:00 | 0 |
| Central Pacific Standard Time | +12:00 | 0 |
| Russia Time Zone 11 | +13:00 | 0 |
| New Zealand Standard Time | +12:00 | 1 |
| UTC+12 | +12:00 | 0 |
| Fiji Standard Time | +12:00 | 0 |
| Kamchatka Standard Time | +13:45 | 0 |
| Chatham Islands Standard Time | +13:00 | 1 |
| UTC+13 | +13:00 | 0 |
| Tonga Standard Time | +14:00 | 0 |
| Samoa Standard Time | +14:00 | 1 |
| Line Islands Standard Time | +14:00 | 0 |
+-----------------------------------+--------------------+------------------+
示例
假设我们使用以下查询的CREATE语句在SQL数据库中创建了一个名为STUDENTS的表：
SQL> CREATE TABLE STUDENTS(ID INT NOT NULL, NAME VARCHAR (200) NOT NULL, AGE INT NOT NULL);
现在，让我们使用下面的INSERT语句向STUDENTS表中插入一些记录，如下所示：
SQL> INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(1, 'Dhruv', '20');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(2, 'Arjun', '23');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(3, 'Dev', '25');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(4, 'Riya', '19');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(5, 'Aarohi','24');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(6, 'Lisa', '20');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(7, 'Roy', '24');
我们可以使用以下查询来验证是否已经创建该表：
SQL> SELECT * FROM STUDENTS;
在SQL数据库中成功创建了表”学生”。
+----+--------+-----+
| ID | NAME | AGE |
+----+--------+-----+
| 1 | Dhruv | 20 |
| 2 | Arjun | 23 |
| 3 | Dev | 25 |
| 4 | Riya | 19 |
| 5 | Aarohi | 24 |
| 6 | Lisa | 20 |
| 7 | Roy | 24 |
+----+--------+-----+
我们可以使用以下查询语句获取学生的当前时区ID –
SQL> SELECT *, CURRENT_TIMEZONE_ID() AS CURRENT_TIMEZONE_IDS FROM STUDENTS;
输出
上述查询的输出如下所示−
+----+--------+-----+----------------------+
| ID | NAME | AGE | CURRENT_TIMEZONE_IDS |
-----+--------+-----+----------------------+
| 1 | Dhruv | 20 | India Standard Time |
| 2 | Arjun | 23 | India Standard Time |
| 3 | Dev | 25 | India Standard Time |
| 4 | Riya | 19 | India Standard Time |
| 5 | Aarohi | 24 | India Standard Time |
| 6 | Lisa | 20 | India Standard Time |
| 7 | Roy | 24 | India Standard Time |
+----+--------+-----+----------------------+