OracleDB入门9：时间-时区详解

开门见山

SQL> select systimestamp from dual;   SYSTIMESTAMP --------------------------------------------------------------------------- 24-JUL-20 01.05.53.444682 PM +00:00 SQL> alter session set time_zone ='-4:00';   Session altered.   SQL>  select systimestamp from dual;   SYSTIMESTAMP --------------------------------------------------------------------------- 24-JUL-20 01.06.04.351449 PM +00:00

SQL> select current_timestamp from dual;   CURRENT_TIMESTAMP --------------------------------------------------------------------------- 24-JUL-20 09.18.28.961463 AM -04:00   SQL> alter session set time_zone ='UTC';   Session altered.   SQL> select current_timestamp from dual;   CURRENT_TIMESTAMP --------------------------------------------------------------------------- 24-JUL-20 01.18.49.432865 PM UTC

SQL> select sysdate from dual;   SYSDATE ------------------- 2020-07-24 13:21:38   SQL> select current_date from dual;   CURRENT_DATE ------------------- 2020-07-24 13:21:49   SQL> alter session set time_zone ='-1:0';   Session altered.   SQL> select current_date from dual;   CURRENT_DATE ------------------- 2020-07-24 12:22:08   SQL> select sysdate from dual;   SYSDATE ------------------- 2020-07-24 13:22:13

SQL> select dbtimezone from dual;   DBTIME ------ +00:00 SQL> select sessiontimezone from dual;   SESSIONTIMEZONE --------------------------------------------------------------------------- -01:00

说明

1. 以sys开头返回的系统时间，与数据库所在的服务器相关；
2. 以current开头返回的客户端时区设置相关的时间，是服务器时间根据时区设置转换而来；
3. 单元格1表示systimestamp不随time_zone设置而变化，其获取的系统的时戳（带时区TIMESTAMP WITH TIME ZONE）；
4. 单元格2表示current_timestamp随time_zone设置而变化，返回的系统时间根据会话时区转换而来；
5. 单元格3 与单元格1/2类似，用于区别系统时间和客户端时区的时间；
6. 单元格4是查询系统时区和会话时区的两个函数