OCP课程18：SQL之管理不同时区下的数据

stonebox · 发表于 2015-12-21 18:29:21

课程目标：

TZ_OFFSET
FROM_TZ
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TO_DSINTERVAL
CURRENT_DATE
CURRENT_TIMESTAMP
LOCALTIMESTAMP
DBTIMEZONE
SESSIONTIMEZONE
EXTRACT

1、时区

时区是地理上的概念，把全球分成24个时区，每一个小时一个时区，定义了某一时刻不同地点的时间。

查看操作系统的时区

[root@oracletest ~]# date -R

Sat, 07 Nov 2015 11:08:16 +0800

如何计算区时

计算的区时=已知区时-（已知区时的时区-要计算区时的时区），（注：东时区为正，西时区为负）。

下面举例加以说明：

例1：已知东京（东九区）时间为5月1日12:00，求北京（东八区）的区时？

北京时间=12:00-（9-8）=11:00（即北京时间为5月1日11:00）。

例2：已知北京时间为5月1日12:00，求伦敦（中时区）的区时？

伦敦时间=12:00-（8-0)=4:00（即伦敦时间为5月1日4:00）。

例3：已知北京时间为5月1日12:00，求纽约（西五区）的区时。

纽约时间=12:00-[8-(-5）]=-1:00+24:00-1天=23:00（即纽约时间为4月30日的23:00）。（注：当算出的区时为负数时，应加上24:00，日期减一天，即从5月1日变为4月30日）。

例4：已知纽约时间为5月1日12:00，求东京的区时？

东京时间=12:00-[(-5）-9]=26:00-24:00+1天=2:00）即东京时间为5月2日2:00）。（注：当算出的区时大于或等于24:00时，应减去24:00，日期加一天，即从5月1日变为5月2日）。

2、time_zone会话参数

Oracle 的时区可以分为两种，一种是数据库的时区，一种是 session 时区。数据库的时区在创建数据库时可以通过在 create database 语句中加上 SET TIME_ZONE = ' { { + | - } hh : mi | time_zone_region } ' 来指定。创建之后，可以通过 alter database 来修改。Session 的时区是根据客户端的时区来决定的，连接以后也可以通过alter session set time_zone来改变，改变的值可以设置为：

一个绝对的偏移值
数据库时区
操作系统的时区
时区的命名区域

例子：使用sessiontimezone函数查看当前session时区

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

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

+08:00

例子：使用dbtimezone函数查看数据库的时区

SQL> select dbtimezone from dual;

DBTIME

------

+00:00

例子：修改当前session的时区为-5：00时区

SQL> alter session set time_zone='-05:00';

Session altered.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

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

-05:00

例子：修改当前session的时区为数据库的时区

SQL> alter session set time_zone=dbtimezone;

Session altered.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

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

+00:00

例子：修改当前session的时区为本地操作系统的时区

SQL> alter session set time_zone=local;

Session altered.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

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

+08:00

例子：修改当前session的时区为某一个时区命名区域

通过v$timezone_names查询时区命名区域

SQL> select tzname from v$timezone_names where lower(tzname) like '%york%';

TZNAME

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

America/New_York

SQL> alter session set time_zone='America/New_York';

Session altered.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

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

America/New_York

3、current_date函数

返回当前会话下面不同时区对应的日期和时间（数据类型为date）

SQL> select current_date from dual;

CURRENT_DATE

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

06-NOV-15

如果要使返回的信息包含时间，需要修改nls_date_format参数

SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select current_date from dual;

CURRENT_DATE

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

06-nov-2015 23:04:29

例子：修改时区后查看current_date的值

SQL> alter session set time_zone='-05:00';

Session altered.

SQL> select sessiontimezone,sysdate,current_date from dual;

SESSIONTIMEZONE SYSDATE CURRENT_DATE

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

-05:00 07-nov-2015 13:46:20 07-nov-2015 00:46:20

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

Session altered.

SQL> select sessiontimezone,sysdate,current_date from dual;

SESSIONTIMEZONE SYSDATE CURRENT_DATE

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

+08:00 07-nov-2015 13:47:10 07-nov-2015 13:47:10

4、current_timestamp函数

返回当前会话下面不同时区对应的日期、时间（包含微秒及上下午）及时区（格式固定，不能修改，数据类型为timestamp with time zone）

SQL> alter session set time_zone='-05:00';

Session altered.

SQL> select sessiontimezone,sysdate,current_timestamp from dual;

SESSIONTIMEZONE SYSDATE CURRENT_TIMESTAMP

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

-05:00 07-nov-2015 13:49:41 07-NOV-15 12.49.41.656386 AM -05:00

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

Session altered.

SQL> select sessiontimezone,sysdate,current_timestamp from dual;

SESSIONTIMEZONE SYSDATE CURRENT_TIMESTAMP

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

+08:00 07-nov-2015 13:52:00 07-NOV-15 01.52.00.084663 PM +08:00

5、localtimestamp函数

返回当前会话下面不同时区对应的日期、时间（包含微秒及上下午，不带时区）（数据类型为timestamp）

SQL> select sessiontimezone,sysdate,current_date,current_timestamp,localtimestamp from dual;

SESSIONTIMEZONE SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP

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

+08:00 07-nov-2015 13:59:24 07-nov-2015 13:59:24 07-NOV-15 01.59.24.639690 PM +08:00 07-NOV-15 01.59.24.639690 PM

SQL> alter session set time_zone='-05:00';

Session altered.

SQL> select sessiontimezone,sysdate,current_date,current_timestamp,localtimestamp from dual;

SESSIONTIMEZONE SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP

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

-05:00 07-nov-2015 13:59:47 07-nov-2015 00:59:47 07-NOV-15 12.59.47.116923 AM -05:00 07-NOV-15 12.59.47.116923 AM

6、timestamp数据类型

是date数据类型的扩展
可以存储微秒
三种类型：timestamp，timestamp with time zone，timestamp with local time zone

登录/注册后可看大图

timestamp类型的值域

登录/注册后可看大图

（1）timestamp类型

timestamp除了年月日时分秒外，还包含微秒，默认精度是6位，最高可以到9位。

例子：比较date数据类型和timestamp数据类型的不同

SQL> conn hr/hr

Connected.

SQL> drop table emp5;

Table dropped.

SQL> create table emp5 as select * from employees;

Table created.

SQL> select hire_date from emp5 where employee_id=100;

HIRE_DATE

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

17-JUN-03

SQL> alter table emp5 modify hire_date timestamp;

Table altered.

SQL> select hire_date from emp5 where employee_id=100;

HIRE_DATE

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

17-JUN-03 12.00.00.000000 AM

（2）timestamp with time zone类型

timestamp with time zone除了包含timestamp的信息外，还带有时区。

登录/注册后可看大图

例子：创建一个表web_orders，存储来自全球的订单信息，包含一个timestamp with time zone类型字段，并插入数据

SQL> create table web_orders(

2 ord_id number primary key,

3 order_date timestamp with time zone);

Table created.

SQL> desc web_orders;

Name Null? Type

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

ORD_ID NOT NULL NUMBER

ORDER_DATE TIMESTAMP(6) WITH TIME ZONE

SQL> select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP

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

+08:00 07-NOV-15 10.15.54.762046 PM +08:00

SQL> insert into web_orders values(1,current_timestamp);

1 row created.

SQL> select * from web_orders;

ORD_ID ORDER_DATE

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

1 07-NOV-15 10.16.46.396682 PM +08:00

SQL> commit;

Commit complete.

另外开一个窗口，模拟来自另外一个地方的订单

SQL> conn hr/hr

Connected.

修改时区

SQL> alter session set time_zone='-05:00';

Session altered.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP

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

07-NOV-15 09.22.49.860132 AM -05:00

插入数据

SQL> insert into web_orders values(2,current_timestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from web_orders;

ORD_ID ORDER_DATE

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

1 07-NOV-15 10.16.46.396682 PM +08:00

2 07-NOV-15 09.23.46.179299 AM -05:00

可以看到时区信息及对应的日期时间保存到记录里面了，在任何客户端查询都不会根据客户端的时区而变化。

（3）timestamp with local time zone

不存储时区信息，时间根据客户端的时区变化而变化

登录/注册后可看大图

例子：创建一个表，存储全球的快递投递时间信息，包含一个timestamp with local time zone类型字段，并插入数据

SQL> create table shipping(delivery_time timestamp with local time zone);

Table created.

SQL> desc shipping;

Name Null? Type

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

DELIVERY_TIME TIMESTAMP(6) WITH LOCAL TIME

ZONE

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP

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

07-NOV-15 10.42.50.916509 PM +08:00

SQL> insert into shipping values(current_timestamp);

SQL> select * from shipping;

DELIVERY_TIME

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

07-NOV-15 10.43.13.949702 PM

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

Session altered.

SQL> select * from shipping;

DELIVERY_TIME

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

07-NOV-15 08.43.13.949702 PM

7、interval数据类型

存储两个日期时间的间隔，有以下两类：

登录/注册后可看大图

interval类型的值域

登录/注册后可看大图

（1）interval year to month数据类型

year后面可以带精度，默认是2位

登录/注册后可看大图

下面是一些示例

登录/注册后可看大图

例子：创建一个表，保存产品的保质期

SQL> create table warranty(

2 prod_id number,

3 warranty_time interval year(3) to month);

Table created.

SQL> desc warranty;

Name Null? Type

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

PROD_ID NUMBER

WARRANTY_TIME INTERVAL YEAR(3) TO MONTH

SQL> insert into warranty values(123,interval '8' month);

1 row created.

SQL> insert into warranty values(155,interval '200' year(3));

1 row created.

SQL> insert into warranty values(678,'200-11');

1 row created.

SQL> select * from warranty;

PROD_ID WARRANTY_TIME

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

123 +000-08

155 +200-00

678 +200-11

（2）interval day to second数据类型

day后面可以带精度，默认是2位

登录/注册后可看大图

下面是一些示例

登录/注册后可看大图

例子：创建一个表，保存实验的间隔时间

SQL> create table lab(

2 exp_id number,

3 test_time interval day(2) to second);

Table created.

SQL> desc lab;

Name Null? Type

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

EXP_ID NUMBER

TEST_TIME INTERVAL DAY(2) TO SECOND(6)

SQL> insert into lab values(100012,'90 00:00:00');

1 row created.

SQL> insert into lab values(56098,interval '6 03:30:16' day to second);

1 row created.

SQL> select * from lab;

EXP_ID TEST_TIME

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

100012 +90 00:00:00.000000

56098 +06 03:30:16.000000

间隔类型单独没有什么用处，一般是与日期时间进行运算

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate ,sysdate+test_time from lab;

SYSDATE SYSDATE+TEST_TIME

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

08-NOV-2015 20:06:19 06-FEB-2016 20:06:19

08-NOV-2015 20:06:19 14-NOV-2015 23:36:35

8、extract函数

从时间日期或者间隔值中抽取特定的时间日期值

登录/注册后可看大图

例子：查询当前的年份

SQL> select sysdate,extract(year from sysdate) from dual;

SYSDATE EXTRACT(YEARFROMSYSDATE)

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

08-NOV-2015 20:16:05 2015

也可以使用to_char函数

SQL> select sysdate,to_char(sysdate,'yyyy') from dual;

SYSDATE TO_C

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

08-NOV-2015 20:16:45 2015

例子：查询人员入职的月份

SQL> select last_name,hire_date,extract(month from hire_date) from employees where manager_id=100;

LAST_NAME HIRE_DATE EXTRACT(MONTHFROMHIRE_DATE)

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

Hartstein 17-FEB-2004 00:00:00 2

也可以使用to_char函数

SQL> select last_name,hire_date,to_char(hire_date,'mm') from employees where manager_id=100;

LAST_NAME HIRE_DATE TO

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

Hartstein 17-FEB-2004 00:00:00 02

9、tz_offset函数

使用该函数将时区区域命名转换成时区值

SQL> select tz_offset('US/Eastern') from dual;

TZ_OFFS

-------

-05:00

SQL> select tz_offset('Canada/Yukon') from dual;

TZ_OFFS

-------

-08:00

SQL> select tz_offset('Europe/London') from dual;

TZ_OFFS

-------

+00:00

前面讲了通过v$timezone_names数据字典视图查询有哪些时区命名区域

SQL> select * from v$timezone_names where tzname like '%Chongqing%';

TZNAME TZABBREV

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

Asia/Chongqing LMT

SQL> select tz_offset('Asia/Chongqing') from dual;

TZ_OFFS

-------

+08:00

10、from_tz函数

将timestamp转换成timestamp with time zone，这个函数用得很少

SQL> select from_tz(timestamp '2000-03-28 08:00:00','3:00') from dual;

FROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00')

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

28-MAR-00 08.00.00.000000000 AM +03:00

SQL> select from_tz(timestamp '2000-03-28 08:00:00','Australia/North') from dual;

FROM_TZ(TIMESTAMP'2000-03-2808:00:00','AUSTRALIA/NORTH')

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

28-MAR-00 08.00.00.000000000 AM AUSTRALIA/NORTH

11、to_timestamp和to_timestamp_tz函数

使用to_timestamp函数将字符串转换成timestamp类型，使用to_timestamp_tz函数将字符串转换成timestamp with time zone类型，带时区。

例子：将字符串转换成timestamp类型

SQL> select to_timestamp('2000-12-01 11:00:00','YYYY-MM-DD HH:MI:SS') from dual;

TO_TIMESTAMP('2000-12-0111:00:00','YYYY-MM-DDHH:MI:SS')

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

01-DEC-00 11.00.00.000000000 AM

例子：将字符串转换成timestamp with time zone类型

SQL> select to_timestamp_tz('1999-12-01 11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM') from dual;

TO_TIMESTAMP_TZ('1999-12-0111:00:00-8:00','YYYY-MM-DDHH:MI:SSTZH:TZM')

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

01-DEC-99 11.00.00.000000000 AM -08:00

12、to_yminterval函数

将字符串转换成年月间隔类型

例子：将入职时间加上1年2个月

SQL> select hire_date,hire_date+to_yminterval('01-02') as hire_date_yminterval from employees where department_id=20;

HIRE_DATE HIRE_DATE_YMINTERVAL

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

17-FEB-2004 00:00:00 17-APR-2005 00:00:00

13、to_dsinterval函数

将字符串转换成天秒间隔类型

例子：将入职时间加上100天10小时

SQL> select last_name,to_char(hire_date,'mm-dd-yy hh:mi:ss') hire_date,to_char(hire_date+to_dsinterval('100 10:00:00'),'mm-dd-yy hh:mi:ss') hiredate2 from employees;

LAST_NAME HIRE_DATE HIREDATE2

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

OConnell 06-21-07 12:00:00 09-29-07 10:00:00

14、相关习题：

（1）You need to create a table for a banking application with the following considerations: 1) You want a column in the table to store the duration of the credit period. 2) The data in the columnshould be stored in a format such that it can be easily added and subtracted with 3) date type data without using the conversion functions. 4) The maximum period of the credit provision in the application is 30 days. 5) The interest has to be calculated for the number of days an individual has taken a credit for. Which data type would you use for such a column in the table？

A.INTERVAL YEAR TO MONTH

B.INTERVAL DAY TO SECOND

C.TIMESTAMP WITH TIME ZONE

D.TIMESTAMP WITH LOCAL TIME ZONE

答案：B

（2）Given below is a list of datetime data types and examples of values stored in them in a random order: Datatype Example 1)INTERVAL YEAR TO MONTH a) '2003-04-15 8:00:00 -8:00' 2)TIMESTAMP WITH LOCAL TIME ZONE b) '+06 03:30:16.000000' 3)TIMESTAMP WITH TIME ZONE c) '17-JUN-03 12.00.00.000000 AM' 4)INTERVAL DAY TO SECOND d) '+02-00' Identify the option that correctly matches the data types with the values.
A.1--d, 2--c, 3--a, 4--b
B.1--b, 2--a, 3--c, 4--d
C.1--b, 2--a, 3--d, 4--c
D.1--d, 2--c, 3--b, 4--a

答案：A

（3）View the Exhibit and examine the description of the PRODUCT_INFORMATION table. You want to display the expiration date of the warranty for a product. Which SQL statement would you execute?

A.SELECT product_id, SYSDATE + warranty_period FROM product_information;

B.SELECT product_id, TO_YMINTERVAL(warranty_period) FROM product_information;

C.SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information;

D.SELECT product_id, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information;

答案：A

（4）Evaluate the SQL statements: CREATE TABLE new_order (orderno NUMBER(4), booking_date TIMESTAMP WITH LOCAL TIME ZONE)；The database is located in San Francisco where the time zone is -8:00. The user is located in New York where the time zone is -5:00. A New York user inserts the following record: INSERT INTO new_order VALUES(1, TIMESTAMP 007-05-10 6:00:00 -5:00 )； Which statement is true ？
A.When the New York user selects the row, booking_date is displayed as '007-05-10 3.00.00.000000'
B.When the New York user selects the row, booking_date is displayed as '2007-05-10 6.00.00.000000 -5:00'.
C.When the San Francisco user selects the row, booking_date is displayed as '007-05-10 3.00.00.000000'
D.When the San Francisco user selects the row, booking_date is displayed as '007-05-10 3.00.00.000000 -8:00'

答案：C

（5）Which three statements are true？(Choose three.)
A.Only one LONG column can be used per table.
B.A TIMESTAMP data type column stores only time values with fractional seconds.
C.The BLOB data type column is used to store binary data in an operating system file.
D.The minimum column width that can be specified for a varchar2 data type column is one.
E.The value for a CHAR data type column is blank-padded to the maximum defined column width.

答案：ADE

（6）Which three possible values can be set for the TIME_ZONE session parameter by using the ALTER SESSION command？(Choose three.)
A.'os'
B.local
C.'-8:00'
D.dbtimezone
E.'Australia'

答案：BCD

（7）Evaluate the following query: SELECT INTERVAL '300' MONTH, INTERVAL '54-2' YEAR TO MONTH, INTERVAL '11:12:10.1234567' HOUR TO SECOND FROM dual；What is the correct output of the above query？
A.+25-00 , +54-02, +00 11:12:10.123457
B.+00-300, +54-02, +00 11:12:10.123457
C.+25-00 , +00-650, +00 11:12:10.123457
D.+00-300 , +00-650, +00 11:12:10.123457

答案：A

帐号		自动登录	找回密码
密码			注册

[认证考试] OCP课程18：SQL之管理不同时区下的数据