Oracle Timezone

数据库参数-TIME_ZONE

Oracle中相关的时区大体可以分为两类：数据库时区和session时区。

select dbtimezone from dual;
ALTER DATABASE SET TIME_ZONE='+08:00'; --修改数据库时区
select sessiontimezone from dual;
ALTER SESSION SET TIME_ZONE='+08:00';  --修改当前会话时区

SQL数据类型

TIMESTAMP WITH TIME ZONE：
TIMESTAMP WITH LOCAL TIME ZONE：以数据库时区时间保存在数据库中，用户请求数据时，以客户端会话(session)时区时间返回。
上述两种数据类型都是timestamp的变种。Date和timestamp数据类型不包含时区信息。

时区相关函数：

DBTIMEZONE returns the value of the database time zone

select dbtimezone from dual;

DBTIME
------
+00:00

SESSIONTIMEZONE returns the value of the current session's time zone.

select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00

CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of data type DATE.

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of data type TIMESTAMP WITH TIME ZONE
show parameter timestamp;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_timestamp_format                 string
nls_timestamp_tz_format              string

show parameter date_format;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string

select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
20-NOV-13 09.32.50.359313 AM -05:00

SYSDATE returns the date and time of the operating system on which the database resides, taking into account the time zone of the database server's operating system that was in effect when the database was started.

SYSTIMESTAMP returns the system date, including fractional seconds and time zone of the system on which the database resides.

select SYSTIMESTAMP from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
20-NOV-13 09.33.11.949260 AM -05:00

select cast(systimestamp as timestamp with local time zone) from dual;

CAST(SYSTIMESTAMPASTIMESTAMPWITHLOCALTIMEZONE)
---------------------------------------------------------------------------
20-NOV-13 10.22.06.301906 AM

The return type of systimestamp and current_timestamp is TIMESTAMP WITH TIME ZONE. Here, systimestamp does not use the dbtimezone, as dbtimezone only affects TIMESTAMP WITH LOCAL TIME ZONE. The systimestamp uses the OS timezone instead.

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of data type TIMESTAMP.

SELECT SYSTIMESTAMP AT TIME ZONE 'UTC' FROM DUAL;

SYSTIMESTAMPATTIMEZONE'UTC'
---------------------------------------------------------------------------
20-NOV-13 02.51.40.952831 PM UTC

Note:
All of the datetime functions that return current system datetime information, such as SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP, and so forth, are evaluated once for each SQL statement, regardless how many times they are referenced in that statement.