ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Timezone

Oracle Timezone

原创 Linux操作系统 作者:chncaesar 时间:2013-11-20 23:25:56 0 删除 编辑
Oracle中相关的时区大体可以分为两类:数据库时区和session时区。
select dbtimezone from dual;
ALTER DATABASE SET TIME_ZONE='+08:00';
select sessiontimezone from dual;
ALTER SESSION SET TIME_ZONE='+08:00';
与时区相关的数据库数据类型:
TIMESTAMP WITH TIME ZONE:
TIMESTAMP WITH LOCAL TIME ZONE:以数据库时区时间保存在数据库中,用户请求数据时,以客户端会话(session)时区时间返回。
上述两种数据类型都是timestamp的变种。Date和timestamp数据类型不包含时区信息。

时区相关函数:
DBTIMEZONE returns the value of the database time zone
SQL> select dbtimezone from dual;

DBTIME
------
+00:00

SESSIONTIMEZONE returns the value of the current session's time zone.
SQL> 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
SQL> show parameter timestamp

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_timestamp_format                 string
nls_timestamp_tz_format              string
SQL> show parameter date_format

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

SQL> select CURRENT_TIMESTAMP from dual;

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

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

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

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.
SQL> select SYSTIMESTAMP from dual;

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

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.

Retrieve the time at specified timezone.
SQL> 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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/638844/viewspace-777156/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Linux Ctrl Z
请登录后发表评论 登录
全部评论

注册时间:2013-07-30

  • 博文量
    102
  • 访问量
    899792