查看: 2015|回复: 0

[Oracle数据库] (Les16 Managing Data in Different Time Zones)[20171230]

发表于 2018-1-10 08:00:01
学习目标: -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';
  1. SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = dbtimezone;
  2. Session altered.
  3. SYS@cqoraxt0>select systimestamp from dual;
  4. SYSTIMESTAMP
  5. ---------------------------------------------------------------------------
  6. 29-DEC-17 10.50.13.546588 PM +08:00 ->当前数据库所在时区的时间
  7. SYS@cqoraxt0>select localtimestamp from dual;
  8. LOCALTIMESTAMP
  9. ---------------------------------------------------------------------------
  10. 29-DEC-17 02.50.38.464141 PM
  11. SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = dbtimezone;
  12. Session altered.
  13. SYS@cqoraxt0>select localtimestamp from dual;
  14. LOCALTIMESTAMP
  15. ---------------------------------------------------------------------------
  16. 29-DEC-17 02.50.49.337510 PM
  17. SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = '-06:00';
  18. Session altered.
  19. SYS@cqoraxt0>select localtimestamp from dual;
  20. LOCALTIMESTAMP
  21. ---------------------------------------------------------------------------
  22. 29-DEC-17 08.51.00.307601 AM->绝对偏移量的时区
  23. SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = local;
  24. Session altered.
  25. SYS@cqoraxt0>select localtimestamp from dual;
  26. LOCALTIMESTAMP
  27. ---------------------------------------------------------------------------
  28. 29-DEC-17 10.51.11.989536 PM->系统本地时间时区
  29. SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = 'America/New_York';
  30. Session altered.
  31. SYS@cqoraxt0>select localtimestamp from dual;
  32. LOCALTIMESTAMP
  33. ---------------------------------------------------------------------------
  34. 29-DEC-17 09.51.21.599491 AM ->指定时间区域名称
复制代码
  CURRENT_DATE,CURRENT_TIMESTAMP,LOCALTIMESTAMP -CURRENT_DATE 当前时间同sysdate表达一样 -CURRENT_TIMESTAMP 含时区     -LOCALTIMESTAMP 不含时区   
  1. col CURRENT_DATE format a30
  2. col SESSIONTIMEZONE format a20
  3. col CURRENT_TIMESTAMP format a40
  4. col LOCALTIMESTAMP format a40
  5. col sysdate format a30
  6. set linesize 300
  7. alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
  8. --调整时间输出的格式
  9. select sessiontimezone,current_date,current_timestamp,localtimestamp,sysdate from dual;
  10. SESSIONTIMEZONE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP SYSDATE
  11. -------------------- ------------------------------ ---------------------------------------- ---------------------------------------- ------------------------------
  12. +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 会话的当前时区
  1. col SESSIONTIMEZONE format a20
  2. col sysdate format a30
  3. set linesize 300
  4. alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
  5. --调整时间输出的格式
  6. select dbtimezone,sessiontimezone,sysdate from dual;
  7. DBTIME SESSIONTIMEZONE SYSDATE
  8. ------ -------------------- ------------------------------
  9. +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多小数秒,时区小时和时区分钟。
  1. create table time_t01(
  2. id number,
  3. t_date date default sysdate,
  4. t_timestamp timestamp default current_timestamp
  5. )
  6. /
  7. insert into time_t01 values(1,default,default);
  8. /
  9. col t_date format a30
  10. col t_timestamp format a40
  11. set linesize 300
  12. alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
  13. --调整会话输出时时间格式
  14. select * from time_t01;
  15. ID T_DATE T_TIMESTAMP
  16. ---------- ------------------------------ ----------------------------------------
  17. 1 2017-12-29 23:51:55 29-DEC-17 11.51.55.085972 PM
复制代码
  1. --添加一个栏位<br>alter table time_t01 add (t_timestamp_zone timestamp with time zone);
  2. insert into time_t01 values(2,default,default,timestamp'2017-12-29 23:57:00 +8:00');
  3. commit;
  4. insert into time_t01 values(3,default,default,timestamp'2017-12-29 23:57:00 America/New_York');
  5. commit;
  6. insert into time_t01 values(4,default,default,timestamp'2017-12-29 23:57:00 -8:00');
  7. commit;
  8. col t_timestamp_zone format a50
  9. select * from time_t01;
  10. ID T_DATE T_TIMESTAMP T_TIMESTAMP_ZONE
  11. ---------- ------------------------------ ---------------------------------------- --------------------------------------------------
  12. 1 2017-12-29 23:51:55 29-DEC-17 11.51.55.085972 PM
  13. 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
  14. 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
  15. 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
  16. --添加一个栏位
  17. alter table time_t01 add (t_timestamp_local_zone timestamp with local time zone);
  18. insert into time_t01 values(5,default,default,current_timestamp,current_timestamp);
  19. col t_timestamp_local_zone format a50
  20. select * from time_t01 order by id;
  21. ID T_DATE T_TIMESTAMP T_TIMESTAMP_ZONE T_TIMESTAMP_LOCAL_ZONE
  22. ---------- ------------------------------ ---------------------------------------- -------------------------------------------------- --------------------------------------------------
  23. 1 2017-12-29 23:51:55 29-DEC-17 11.51.55.085972 PM
  24. 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
  25. 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
  26. 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
  27. 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
  28. <strong>改变会话当前时区,</strong><strong>timestamp with</strong><strong> local time zone 时间也会跟着变动</strong>
  29. SYS@cqoraxt0>alter session set time_zone='-09:00';
  30. Session altered.
  31. SYS@cqoraxt0> select * from time_t01 order by id;
  32. ID T_DATE T_TIMESTAMP T_TIMESTAMP_ZONE T_TIMESTAMP_LOCAL_ZONE
  33. ---------- ------------------------------ ---------------------------------------- -------------------------------------------------- --------------------------------------------------
  34. 1 2017-12-29 23:51:55 29-DEC-17 11.51.55.085972 PM
  35. 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
  36. 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
  37. 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
  38. 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
  39. <br>
复制代码

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)

  1. create table time_t02(
  2. id number,
  3. t_interval_y interval year to month,
  4. t_interval_d interval day to second
  5. )
  6. /
  7. insert into time_t02 values(1,'03-5' ,'05 12:00:00')
  8. /
  9. insert into time_t02 values(2,interval '04' year,interval '06 13:00:00' day to second )
  10. /
  11. insert into time_t02 values(3,interval '12' month,interval '07 14:00:00' day to second )
  12. /
  13. alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
  14. col t_interval_y format a30
  15. col t_interval_d format a30
  16. select * from time_t02;
  17. ID T_INTERVAL_Y T_INTERVAL_D
  18. ---------- ------------------------------ ------------------------------
  19. 1 +03-05 +05 12:00:00.000000
  20. 2 +04-00 +06 13:00:00.000000
  21. 3 +01-00 +06 13:00:00.000000
复制代码
EXTRACT 提取时间 -可提取时间类型 EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND] [TIMEZONE_HOUR] [TIMEZONE_MINUTE] [TIMEZONE_REGION] [TIMEZONE_ABBR] -年、月、天、小时、分钟、秒、时区小时、时区分钟、时区区域、时区缩写
  1. set linezie 600
  2. col zone_region format a20
  3. col zone_abbr format a20
  4. select
  5. extract(year from t_timestamp_zone) year,
  6. extract(month from t_timestamp_zone) month,
  7. extract(day from t_timestamp_zone) day,
  8. extract(hour from t_timestamp_zone) hour,
  9. extract(minute from t_timestamp_zone) minute,
  10. extract(second from t_timestamp_zone) second,
  11. extract(timezone_hour from t_timestamp_zone) zone_hour,
  12. extract(timezone_minute from t_timestamp_zone) zone_minute,
  13. extract(timezone_region from t_timestamp_zone) zone_region,
  14. extract(timezone_abbr from t_timestamp_zone) zone_abbr
  15. from time_t01
  16. where id=3
  17. /
  18. YEAR MONTH DAY HOUR MINUTE SECOND ZONE_HOUR ZONE_MINUTE
  19. ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
  20. ZONE_REGION ZONE_ABBR
  21. -------------------- --------------------
  22. 2017 12 30 4 57 0 -5 0
  23. America/New_York EST
复制代码
时区转换函数 -TZ_OFFSET 时间区域转为为数值 TZ_OFFSET ( ['time_zone_name'] '[+ | -] hh:mm' ] [ SESSIONTIMEZONE] [DBTIMEZONE]
  1. SYS@cqoraxt0>select tz_offset('America/New_York') from dual;
  2. TZ_OFFS
  3. -------
  4. -05:00
复制代码
V$TIMEZONE_NAMES所有时区区域名称视图 -FROM_TZ 将TIMESTAMP转换为TIMESTAMP WITH TIME ZONE FROM_TZ(TIMESTAMP timestamp_value, time_zone_value)
  1. SYS@cqoraxt0>select from_tz(timestamp '2017-12-30 23:50:00','+8:00') from dual;
  2. FROM_TZ(TIMESTAMP'2017-12-3023:50:00','+8:00')
  3. ---------------------------------------------------------------------------
  4. 30-DEC-17 11.50.00.000000000 PM +08:00
复制代码
-TO_TIMESTAMP 将字符串转为TIMESTAMP时间类型 TO_TIMESTAMP (char,[fmt],['nlsparam'])
  1. SYS@cqoraxt0>select to_timestamp('2017-12-30 23:53:00','YYYY-MM-DD HH24:MI:SS') from dual;
  2. TO_TIMESTAMP('2017-12-3023:53:00','YYYY-MM-DDHH24:MI:SS')
  3. ---------------------------------------------------------------------------
  4. 30-DEC-17 11.53.00.000000000 PM
复制代码

-TO_TIMESTAMP_TZ 将字符串转为TIMESTAMP WITH TIME ZONE TO_TIMESTAMP_TZ (char,[fmt],['nlsparam'])
  1. SYS@cqoraxt0>select to_timestamp_tz('2017-12-30 23:55:00 +8:00','YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual;
  2. TO_TIMESTAMP_TZ('2017-12-3023:55:00+8:00','YYYY-MM-DDHH24:MI:SSTZH:TZM')
  3. ---------------------------------------------------------------------------
  4. 30-DEC-17 11.55.00.000000000 PM +08:00
复制代码
-TO_YMINTERVAL 将字符串转为时间间隔类型INTERVAL YEAR TO MONTH TO_YMINTERVAL(char)
  1. SYS@cqoraxt0>select sysdate+to_yminterval('01-01') from dual;
  2. SYSDATE+TO_YMINTERV
  3. -------------------
  4. 2019-01-31 00:03:14
复制代码

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

TO_DSINTERVAL(char)
  1. SYS@cqoraxt0>select sysdate+to_dsinterval('05 1:00:00') from dual;
  2. SYSDATE+TO_DSINTERV
  3. -------------------
  4. 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







回复

使用道具 举报