(Les16 Managing Data in Different Time Zones)[20171230]

2017-12-31 14:15:32来源:cnblogs.com作者:请点..头像人点击

分享
学习目标:    -current_date 当前时间 TZ_OFFSET    -current_timestamp 含小数秒当前时间    FROM_TZ     -localtimestamp 含小数秒本地时间    TO_TIMESTAMP    -dbtimezone 数据库时区    TO_TIMSTAMP_TZ    -sessiontimezone 回话时间时区    TO_YMINTERVAL    -extract 提取    TO_DSINTERVAL  TIME_ZONE会话参数    可以设定为以下值        -一个绝对偏移值        -数据库时区        -系统本地时区        -一个区域名称            ALTER SESSION SET TIME_ZONE = '-06:00';        ALTER SESSION SET TIME_ZONE = dbtimezone;        ALTER SESSION SET TIME_ZONE = local;        ALTER SESSION SET TIME_ZONE = 'America/New_York'; 
SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = dbtimezone;Session altered.SYS@cqoraxt0>select systimestamp from dual;SYSTIMESTAMP---------------------------------------------------------------------------29-DEC-17 10.50.13.546588 PM +08:00 ->当前数据库所在时区的时间SYS@cqoraxt0>select localtimestamp from dual;LOCALTIMESTAMP---------------------------------------------------------------------------29-DEC-17 02.50.38.464141 PMSYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = dbtimezone;Session altered.SYS@cqoraxt0>select localtimestamp from dual;LOCALTIMESTAMP---------------------------------------------------------------------------29-DEC-17 02.50.49.337510 PMSYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = '-06:00';Session altered.SYS@cqoraxt0>select localtimestamp from dual;LOCALTIMESTAMP---------------------------------------------------------------------------29-DEC-17 08.51.00.307601 AM->绝对偏移量的时区SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = local;Session altered.SYS@cqoraxt0>select localtimestamp from dual;LOCALTIMESTAMP---------------------------------------------------------------------------29-DEC-17 10.51.11.989536 PM->系统本地时间时区SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = 'America/New_York';Session altered.SYS@cqoraxt0>select localtimestamp from dual;LOCALTIMESTAMP---------------------------------------------------------------------------29-DEC-17 09.51.21.599491 AM ->指定时间区域名称 
     CURRENT_DATE,CURRENT_TIMESTAMP,LOCALTIMESTAMP            -CURRENT_DATE 当前时间同sysdate表达一样            -CURRENT_TIMESTAMP 含时区    -LOCALTIMESTAMP 不含时区               
col CURRENT_DATE format a30            col SESSIONTIMEZONE format a20            col CURRENT_TIMESTAMP format a40            col LOCALTIMESTAMP format a40            col sysdate format a30            set linesize 300            alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';             --调整时间输出的格式            select sessiontimezone,current_date,current_timestamp,localtimestamp,sysdate from dual;        SESSIONTIMEZONE      CURRENT_DATE                   CURRENT_TIMESTAMP                        LOCALTIMESTAMP                           SYSDATE-------------------- ------------------------------ ---------------------------------------- ---------------------------------------- ------------------------------+08:00               2017-12-29 23:13:08            29-DEC-17 11.13.08.826336 PM +08:00      29-DEC-17 11.13.08.826336 PM             2017-12-29 23:13:08 
    DBTIMEZONE,SESSIONTIMEZONE            -DBTIMEZONE 数据库时区,也就是0时区            -SESSIONTIMEZONE 会话的当前时区          
              col SESSIONTIMEZONE format a20            col sysdate format a30            set linesize 300            alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';             --调整时间输出的格式            select dbtimezone,sessiontimezone,sysdate from dual;DBTIME SESSIONTIMEZONE      SYSDATE------ -------------------- ------------------------------+00:00 +08:00               2017-12-29 23:16:04 
TIMESTAMP 时间类型        -TIMESTAMP [(fractional_seconds_precision)] 包含:年,月,天,小时,分钟,秒,时区小时,时区分钟        -TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE        -TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE            TIMESTAMP时间类型各个值的范围            DATE,TIMESTAMP的不同点            -TIMESTAMP相比DATE多小数秒,时区小时和时区分钟。 
create table time_t01(id number,t_date date default sysdate,t_timestamp timestamp default current_timestamp)/insert into time_t01 values(1,default,default);/col t_date format a30col t_timestamp format a40set linesize 300alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';--调整会话输出时时间格式select * from time_t01;         ID T_DATE                         T_TIMESTAMP---------- ------------------------------ ----------------------------------------         1 2017-12-29 23:51:55            29-DEC-17 11.51.55.085972 PM
        
--添加一个栏位
alter table time_t01 add (t_timestamp_zone timestamp with time zone); insert into time_t01 values(2,default,default,timestamp'2017-12-29 23:57:00 +8:00');commit;insert into time_t01 values(3,default,default,timestamp'2017-12-29 23:57:00 America/New_York');commit;insert into time_t01 values(4,default,default,timestamp'2017-12-29 23:57:00 -8:00');commit;col t_timestamp_zone format a50select * from time_t01; ID T_DATE T_TIMESTAMP T_TIMESTAMP_ZONE---------- ------------------------------ ---------------------------------------- -------------------------------------------------- 1 2017-12-29 23:51:55 29-DEC-17 11.51.55.085972 PM 2 2017-12-29 23:57:39 29-DEC-17 11.57.39.696414 PM 29-DEC-17 11.57.00.000000 PM +08:00 3 2017-12-30 00:02:51 30-DEC-17 12.02.51.376596 AM 29-DEC-17 11.57.00.000000 PM AMERICA/NEW_YORK 4 2017-12-30 00:02:51 30-DEC-17 12.02.51.385169 AM 29-DEC-17 11.57.00.000000 PM -08:00 --添加一个栏位 alter table time_t01 add (t_timestamp_local_zone timestamp with local time zone); insert into time_t01 values(5,default,default,current_timestamp,current_timestamp); col t_timestamp_local_zone format a50 select * from time_t01 order by id; ID T_DATE T_TIMESTAMP T_TIMESTAMP_ZONE T_TIMESTAMP_LOCAL_ZONE---------- ------------------------------ ---------------------------------------- -------------------------------------------------- -------------------------------------------------- 1 2017-12-29 23:51:55 29-DEC-17 11.51.55.085972 PM 2 2017-12-29 23:57:39 29-DEC-17 11.57.39.696414 PM 29-DEC-17 11.57.00.000000 PM +08:00 3 2017-12-30 00:02:51 30-DEC-17 12.02.51.376596 AM 29-DEC-17 11.57.00.000000 PM AMERICA/NEW_YORK 4 2017-12-30 00:02:51 30-DEC-17 12.02.51.385169 AM 29-DEC-17 11.57.00.000000 PM -08:00 5 2017-12-30 00:07:05 30-DEC-17 12.07.05.688410 AM 30-DEC-17 12.07.05.688410 AM +08:00 30-DEC-17 12.07.05.688410 AM 改变会话当前时区,timestamp with local time zone 时间也会跟着变动 SYS@cqoraxt0>alter session set time_zone='-09:00'; Session altered. SYS@cqoraxt0> select * from time_t01 order by id; ID T_DATE T_TIMESTAMP T_TIMESTAMP_ZONE T_TIMESTAMP_LOCAL_ZONE---------- ------------------------------ ---------------------------------------- -------------------------------------------------- -------------------------------------------------- 1 2017-12-29 23:51:55 29-DEC-17 11.51.55.085972 PM 2 2017-12-29 23:57:39 29-DEC-17 11.57.39.696414 PM 29-DEC-17 11.57.00.000000 PM +08:00 3 2017-12-30 00:02:51 30-DEC-17 12.02.51.376596 AM 29-DEC-17 11.57.00.000000 PM AMERICA/NEW_YORK 4 2017-12-30 00:02:51 30-DEC-17 12.02.51.385169 AM 29-DEC-17 11.57.00.000000 PM -08:00 5 2017-12-30 00:07:05 30-DEC-17 12.07.05.688410 AM 30-DEC-17 12.07.05.688410 AM +08:00 29-DEC-17 07.07.05.688410 AM

    INTERVAL时间类型        -INTERVAL用于存储两个时间差        -年-月 Year-month    INTERVAL YEAR TO MONTH        -天-时间 Day-time     INTERVAL DAY TO SECOND         INTERVAL YEAR (year_precision) TO MONTH        INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)        
create table time_t02(id number,t_interval_y interval year to month,t_interval_d interval day to second)/insert into time_t02 values(1,'03-5' ,'05 12:00:00')/insert into time_t02 values(2,interval '04' year,interval '06 13:00:00' day to second )/insert into time_t02 values(3,interval '12' month,interval '07 14:00:00' day to second )/  alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';col t_interval_y format a30col t_interval_d format a30select * from time_t02;         ID T_INTERVAL_Y                   T_INTERVAL_D---------- ------------------------------ ------------------------------         1 +03-05                         +05 12:00:00.000000         2 +04-00                         +06 13:00:00.000000         3 +01-00                         +06 13:00:00.000000
     EXTRACT 提取时间        -可提取时间类型            EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND] [TIMEZONE_HOUR] [TIMEZONE_MINUTE] [TIMEZONE_REGION] [TIMEZONE_ABBR]                -年、月、天、小时、分钟、秒、时区小时、时区分钟、时区区域、时区缩写
set linezie 600col zone_region format a20col zone_abbr format a20select    extract(year from t_timestamp_zone) year,    extract(month from t_timestamp_zone) month,    extract(day from t_timestamp_zone) day,    extract(hour from t_timestamp_zone) hour,    extract(minute from t_timestamp_zone) minute,    extract(second from t_timestamp_zone) second,    extract(timezone_hour from t_timestamp_zone) zone_hour,    extract(timezone_minute from t_timestamp_zone) zone_minute,    extract(timezone_region from t_timestamp_zone) zone_region,    extract(timezone_abbr from t_timestamp_zone) zone_abbr    from time_t01where id=3/                              YEAR      MONTH        DAY       HOUR     MINUTE     SECOND  ZONE_HOUR ZONE_MINUTE---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------ZONE_REGION          ZONE_ABBR-------------------- --------------------      2017         12         30          4         57          0         -5           0America/New_York     EST
     时区转换函数        -TZ_OFFSET 时间区域转为为数值            TZ_OFFSET ( ['time_zone_name'] '[+ | -] hh:mm' ]      [ SESSIONTIMEZONE] [DBTIMEZONE] 
SYS@cqoraxt0>select tz_offset('America/New_York') from dual;TZ_OFFS--------05:00
            V$TIMEZONE_NAMES所有时区区域名称视图         -FROM_TZ 将TIMESTAMP转换为TIMESTAMP WITH TIME ZONE            FROM_TZ(TIMESTAMP timestamp_value, time_zone_value)
SYS@cqoraxt0>select from_tz(timestamp '2017-12-30 23:50:00','+8:00') from dual;FROM_TZ(TIMESTAMP'2017-12-3023:50:00','+8:00')---------------------------------------------------------------------------30-DEC-17 11.50.00.000000000 PM +08:00
        -TO_TIMESTAMP 将字符串转为TIMESTAMP时间类型            TO_TIMESTAMP (char,[fmt],['nlsparam'])  
SYS@cqoraxt0>select to_timestamp('2017-12-30 23:53:00','YYYY-MM-DD HH24:MI:SS') from dual;TO_TIMESTAMP('2017-12-3023:53:00','YYYY-MM-DDHH24:MI:SS')---------------------------------------------------------------------------30-DEC-17 11.53.00.000000000 PM
        -TO_TIMESTAMP_TZ 将字符串转为TIMESTAMP WITH TIME ZONE            TO_TIMESTAMP_TZ (char,[fmt],['nlsparam']) 
SYS@cqoraxt0>select to_timestamp_tz('2017-12-30 23:55:00 +8:00','YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual;           TO_TIMESTAMP_TZ('2017-12-3023:55:00+8:00','YYYY-MM-DDHH24:MI:SSTZH:TZM')---------------------------------------------------------------------------30-DEC-17 11.55.00.000000000 PM +08:00 
        -TO_YMINTERVAL 将字符串转为时间间隔类型INTERVAL YEAR TO MONTH            TO_YMINTERVAL(char)         
 SYS@cqoraxt0>select sysdate+to_yminterval('01-01') from  dual;SYSDATE+TO_YMINTERV-------------------2019-01-31 00:03:14 

        -TO_DSINTERVAL 将字符串转为时间间隔类型INTERVAL DAY TO SECOND

            TO_DSINTERVAL(char)          
 SYS@cqoraxt0>select sysdate+to_dsinterval('05 1:00:00') from dual;            SYSDATE+TO_DSINTERV            -------------------            2018-01-05 01:06:57

学习总结:        1.5个时间current_date、current_timestamp、localtimestamp、dbtimezone、sessionzone        2.时区值的更改及对应可选值数据库时区、系统时区、偏移量值、时区区域名称        3.时间类型:DATE ,TIMESTAMP,TIMESTAMP WITH TIME ZONE ,TIMESTAMP WITH LOCAL TIME ZONE,INTERVAL YEAR TO MONTH,INTERVAL DAY TO SECOND        4.时间类型中的各个值的提取函数EXTRACT        5.时间类型的转换函数TO_DATE,TZ_OFFSET,FROM_TZ,TO_TIMESTAMP,TO_TIMSTAMP_TZ,TO_YMINTERVAL,TO_DSINTERVAL





最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台