SQL之时区、时间戳相关的函数

一、时区相关概念（数据库）

1、可能涉及的3个时区概念

a.UTC时间，标准时区,其他地区的时区根据时间与UTC的差距，做偏移量，如New York晚于UTC 5小时，则为-5:00。

 Oracle建议将数据库时区设置为UTC

b.数据库时区，安装数据库的位置所在的时区，可通过函数DBTIMEZONE获取

c.会话时区，用户所在的时区，可通过函数SESSIONTIMEZONE

2、与日期和时区相关的数据类型：

DATE

TIMESTAMP

同DATE类型，但是增加了含有小数点的秒，提高精度

TIMESTAMP WITH TIME ZONE

类似TIMESTAMP，但附加了TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR信息。

TIMESTAMP WITH LOCAL TIME ZONE

与TIMESTAMP，不包含时区相关的数据，当查询TIMESTAMP WITH LOCAL TIME ZONE类型数据时，数据将以用户本地的时区，展示给用户。

3、TIME ZONE的两种保存方式：

通过时区名保存，如'Asia/Shanghai'、

通过本地时间与UTC时间的偏移量保存，如'+08:00'

4、数据库中跟踪时区的两个变量：

Database time zone、

Session time zone

1）查看

SQL> select dbtimezone,sessiontimezone from dual;

  DBTIMEZONE SESSIONTIMEZONE

---------- ---------------------

+00:00     +08:00

2）改变数据库时区

SQL> alter database set time_zone='+01:00';

alter database set time_zone='+01:00'

  ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

注：如果数据库中含有包含TIMESTAMP WITH LOCAL TIME ZONE类型列的表，则无法改变数据库时区（database time zone）

以下查询将定位这样的表

SQL> SELECT   OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE 

    FROM     DBA_TAB_COLUMNS 

    WHERE    DATA_TYPE LIKE '%LOCAL TIME_ZONE%' 

    ORDER BY OWNER, TABLE_NAME, COLUMN_NAME;

OWNER   TABLE_NAME   COLUMN_NAME      DATA_TYPE

------- ------------ --------------- --------------------------------

OE    ORDERS     ORDER_DATE      TIMESTAMP(6) WITH LOCAL TIME ZONE

 改变会话时区

SQL> alter session set time_zone='+08:00';

  Session altered

小建议：当应用程序需要处理多个时区和相关的数据类型时，通常将数据库的时区设置为UTC，而不必考虑数据库服务器实际所处位置的本地时区。

二、时区函数

1）DBTIMEZONE

语法：DBTIMEZONE

参数：无参数

返回：返回字符类型数据

作用：返回数据库所在位置的时区

实例：

返回当前数据的时区，根据结果可知，当前数据库的时区被设置为UTC

SQL> select dbtimezone from dual;

  DBTIMEZONE

----------

+00:00

2) SESSIONTIMEZONE

语法：SESSIONTIMEZONE

参数：无参数

返回：返回字符类型数据

作用：返回当前会话的时区

实例：

SQL> select sessiontimezone from dual;

  SESSIONTIMEZONE

------------------------------------------

+08:00

3) CURRENT_DATE、CURRENT_TIMESTAMP

语法：CURRENT_DATE, CURRENT_TIMESTAMP(t)

参数：CURRENT_DATE无参数；

     CURRENT_TIMESTAMP中的参数t，指定本地时区小数点后的精度，范围1-9。可选项，默认为6