TIMESTAMP WITH TIME ZONE与TIMESTAMP WITH LOCAL TIME ZONE数据类型
来源:互联网 发布:淘宝上图片模糊怎么办 编辑:程序博客网 时间:2024/05/18 14:26
TIMESTAMP WITH TIME ZONE与TIMESTAMP WITH LOCAL TIME ZONE数据类型
TIMESTAMP WITH TIME ZONE Datatype
TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This datatype is useful for collecting and evaluating date information across geographic regions.
TIMESTAMP WITH TIME ZONE存储时区信息。
TIMESTAMP WITH LOCAL TIME ZONE Datatype
TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time zone offset in its value. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This datatype is useful for displaying date information in the time zone of the client system in a two-tier application.
TIMESTAMP WITH LOCAL TIME ZONE 将时间数据以数据库时区进行规范化后进行存储(即将时间数据转换为数据库时区的时间数据进行存储),但不存储时区信息;客户端检索时,oracle会将数据库中存储的时间数据转换为客户端session时区的时间数据后返回给客户端。
下面的例子能很好的区别TIMESTAMP WITH TIME ZONE与TIMESTAMP WITH LOCAL TIME ZONE数据类型:
SQL> select dbtimezone from dual; --数据库时区DBTIME------+00:00SQL> select sessiontimezone from dual; --session会话时区SESSIONTIMEZONE---------------------------------------------------------------------------+08:00SQL> create table timezone_test(id int, t1 timestamp with time zone,t2 timestamp with local time zone);表已创建。SQL> select systimestamp from dual;SYSTIMESTAMP---------------------------------------------------------------------------06-2月 -12 10.22.45.697000 上午 +08:00SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';会话已更改。SQL> select systimestamp from dual;SYSTIMESTAMP---------------------------------------------------------------------------06-2月 -12 10.24.13.446000 上午 +08:00SQL> select sysdate from dual;SYSDATE-------------------2012-02-06 10:24:25SQL> insert into timezone_test values(1,TO_TIMESTAMP('2012-2-6 14:10:10.123000', 2 'YYYY-MM-DD HH24:MI:SS.FF'),TO_TIMESTAMP('2012-2-6 14:10:10.123000', 'YYYY-MM-DD HH24:MI:SS.FF'));已创建 1 行。SQL> insert into timezone_test values(2,TO_TIMESTAMP('2012-2-6 5:10:10.123000', 2 'YYYY-MM-DD HH24:MI:SS.FF'),TO_TIMESTAMP('2012-2-6 5:10:10.123000', 'YYYY-MM-DD HH24:MI:SS.FF'));已创建 1 行。SQL> insert into timezone_test values(3,TO_DATE('2012-2-6 14:10:10', 2 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2012-2-6 14:10:10', 'YYYY-MM-DD HH24:MI:SS'));已创建 1 行。SQL> commit;提交完成。SQL> set linesize 120SQL> col t1 for a50SQL> col t2 for a50SQL> select * from timezone_test; ID T1 T2---------- -------------------------------------------------- -------------------------------------------------- 1 06-2月 -12 02.10.10.123000 下午 +08:00 06-2月 -12 02.10.10.123000 下午 2 06-2月 -12 05.10.10.123000 上午 +08:00 06-2月 -12 05.10.10.123000 上午 3 06-2月 -12 02.10.10.000000 下午 +08:00 06-2月 -12 02.10.10.000000 下午SQL> select sessiontimezone from dual;SESSIONTIMEZONE---------------------------------------------------------------------------+08:00--设置不同的时区,观察两种数据类型的区别。SQL> alter session set time_zone='+06:00'; 会话已更改。SQL> select * from timezone_test; ID T1 T2---------- -------------------------------------------------- -------------------------------------------------- 1 06-2月 -12 02.10.10.123000 下午 +08:00 06-2月 -12 12.10.10.123000 下午 2 06-2月 -12 05.10.10.123000 上午 +08:00 06-2月 -12 03.10.10.123000 上午 3 06-2月 -12 02.10.10.000000 下午 +08:00 06-2月 -12 12.10.10.000000 下午SQL> alter session set time_zone='+08:00';会话已更改。SQL> select * from timezone_test; ID T1 T2---------- -------------------------------------------------- -------------------------------------------------- 1 06-2月 -12 02.10.10.123000 下午 +08:00 06-2月 -12 02.10.10.123000 下午 2 06-2月 -12 05.10.10.123000 上午 +08:00 06-2月 -12 05.10.10.123000 上午 3 06-2月 -12 02.10.10.000000 下午 +08:00 06-2月 -12 02.10.10.000000 下午SQL> alter session set time_zone='+09:00';会话已更改。SQL> select * from timezone_test; ID T1 T2---------- -------------------------------------------------- -------------------------------------------------- 1 06-2月 -12 02.10.10.123000 下午 +08:00 06-2月 -12 03.10.10.123000 下午 2 06-2月 -12 05.10.10.123000 上午 +08:00 06-2月 -12 06.10.10.123000 上午 3 06-2月 -12 02.10.10.000000 下午 +08:00 06-2月 -12 03.10.10.000000 下午SQL> alter session set time_zone='+07:00';会话已更改。SQL> select * from timezone_test; ID T1 T2---------- -------------------------------------------------- -------------------------------------------------- 1 06-2月 -12 02.10.10.123000 下午 +08:00 06-2月 -12 01.10.10.123000 下午 2 06-2月 -12 05.10.10.123000 上午 +08:00 06-2月 -12 04.10.10.123000 上午 3 06-2月 -12 02.10.10.000000 下午 +08:00 06-2月 -12 01.10.10.000000 下午SQL>
参考:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i53219
- TIMESTAMP WITH TIME ZONE与TIMESTAMP WITH LOCAL TIME ZONE数据类型
- timestamp with time zone 和 timestamp with local time zone
- TIMESTAMP WITH TIME ZONE &TIMESTAMP WITH LOCAL TIME ZONE
- timestamp with local time zone类型和timestamp with time zone
- timestamp with local time zone 和 timesatamp with time zone的区别
- ocp-047-30 timestamp with local time zone 和timesatamp with time zone
- JAVA获取数据库TIMESTAMP(6) WITH LOCAL TIME ZONE
- Oracle TIMESTAMP WITH TIME ZONE类型
- [每日一题] OCP1z0-047 :2013-08-26 TIMESTAMP WITH LOCAL TIME ZONE....................112
- INTERVAL YEAR TO MONTH和TIMESTAMP WITH TIME ZONE 等
- ORA-30078: partition bound must be TIME/TIMESTAMP WITH TIME ZONE literals
- Time with Time-zone Programming on Linux
- Time Zone
- Time Zone
- Tips for working with time zone in flex app
- Resetting time zone
- rails time zone (brown)
- device time zone
- Java Web快速开发框架 ---- JSPX
- 学习资料
- Designing for Touch Screen
- Oracle 11g R2 OEM无法连接到数据库
- ubuntu 五笔输入法
- TIMESTAMP WITH TIME ZONE与TIMESTAMP WITH LOCAL TIME ZONE数据类型
- 个人资料查询
- 深入解读Linux与Android的相互关系
- python 取出当前时间详解 time
- VC中添加WM_USER的消息响应函数
- 簽核流程設置
- win7 下移动硬盘不完全使用,显示
- 破坏者或建设者
- .net环境变量 的设置