SQL 基础之时区函数（二十一）

wuliws

　　使用数据类型来存储两个日期时间值之间的时间差
　　使用下列的日期时间函数：
　　– CURRENT_DATE
　　– CURRENT_TIMESTAMP
　　– LOCALTIMESTAMP
　　– DBTIMEZONE
　　– SESSIONTIMEZONE
　　– EXTRACT
　　– TZ_OFFSET
　　– FROM_TZ
　　– TO_TIMESTAMP
　　– TO_YMINTERVAL
　　– TO_DSINTERVAL
　　TIME_ZONE 可以设置为：
　　绝对偏移量
　　数据库的时区
　　OS本地时区
　　区域名
　　alter session set time_zone = '-05:00';
　　alter session set time_zone = dbtimezone;
　　alter session set time_zone = local;
　　alter session set time_zone = 'America/New_York';
　　TIMESTAMP 数据类型
数据类型范围TIMESTAMP年，月，日，时，分，秒与秒的小数部分TIMESTAMP WITH TIME ZONE与TIMESTAMP数据类型相同；还包括：TIMEZONE_HOUR，TIMEZONE_MINUTE或TIMEZONE_REGION　　TIMESTAMP WITH LOCAL TIME ZONE
　　存储类型与 TIMESTAMP 相似，在用户提交时间给数据库的时，该类型会转换成数据库的时区来保存数据，即数据库保存的时间是数据库本地时区，当用户访问数据库时 oracle 会自动将该时间转换成当前客户端的时间
　　TIMESTAMP 字段
Datetime  字段有效值YEAR–4712 to 9999 (不包括0年）MONTH01 to 12DAY01 to 31HOUR00 to 23MINUTE00 to 59SECOND00 to 59.9(N) -- 注：9(N)为精度TIMEZONE_HOUR-12 to 14 TIMEZONE_MINUTE00 to 59　　create table web_orders (order_date timestamp with time zone,delivery_time timestamp with local time zone);
　　insert into web_orders values (current_date, current_timestamp + 2);
　　select * from web_orders;
　　DATE 与 TIMESTAMP的区别
　　select hire_date from employees;

　　alter table employees modify hire_date timestamp;
　　select hire_date from employees;

• 　　CURRENT_DATE:
  

　　– 从用户会话返回当前的日期
　　– 返回的是DATE数据类型

• 　　CURRENT_TIMESTAMP:
  

　　– 从用户会话返回当前的日期和时间
　　– 返回的是TIMESTAMP WITH TIME ZONE数据类型

• 　　LOCALTIMESTAMP:
  

　　– 从用户会话返回当前的日期和时间
　　– 返回的是TIMESTAMP数据类型
　　比较会话的时区的日期和时间
　　将参数TIME_ZONE设置为–5:00，然后使用SELECT 语句查看每个日期和时间的差异比较。
　　alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
　　alter session set time_zone = '-5:00';
　　select sessiontimezone, current_date from dual;
　　SESSIONTIMEZONE    CURRENT_DATE
　　--------------------------- --------------------------------------------------
　　-05:00                            27-3  -2017 01:12:37
　　select sessiontimezone, current_timestamp from dual;
　　SESSIONTIMEZONE    CURRENT_TIMESTAMP
　　--------- ---------------------------------------------------------------------------
　　-05:00                            27-3  -17 01.13.23.473132  -05:00
　　select sessiontimezone, localtimestamp from dual;
　　SESSIONTIMEZONE    LOCALTIMESTAMP
　　------------ ---------------------------------------------------------------------------
　　-05:00                           27-3  -17 01.14.06.470998
　　DBTIMEZONE 和 和 SESSIONTIMEZONE
　　显示数据库时区：
　　select dbtimezone from dual;
　　DBTIMEZONE
　　------------------
　　+00:00
　　显示会话时区：
　　select sessiontimezone from dual;
　　INTERVAL 数据类型
　　INTERVAL 数据类型用于存储两个日期的差值。
　　有两种类型的间隔:
　　– Year-month
　　– Day-time
　　时间间隔的精度：
　　– 实际的范围子集构成的间隔
　　– 指定的时间间隔
数据类型范围INTERVAL YEAR TO MONTH年、月INTERVAL DAY TO SECOND天、小时、分钟、秒及小数部分　　INTERVAL 范围
INTERVAL范围间隔有效值YEAR任何正、负的整数MONTH00 to 11DAY任何正、负的整数HOUR00 to 23MINUTE00 to 59SECOND00 to 59.9(N) –注：9(N)为精度　　INTERVAL YEAR TO MONTH ：示例
　　create table warranty (prod_id number, warranty_time interval year(3) to month);
　　insert into warranty values (123, interval '8' month);
　　insert into warranty values (155, interval '200' year(3));
　　insert into warranty values (678, '200-11');
　　select * from warranty;
　　INTERVAL DAY TO SECOND 示例
　　create table lab ( exp_id number, test_time interval day(2) to second);
　　insert into lab values (100012, '90 00:00:00');
　　insert into lab values (56098,
　　interval '6 03:30:16' day to second);
　　EXTRACT
　　从SYSDATE显示年份：
　　select extract (year from sysdate) from dual;
　　显示MANAGER_ID为100的员工的HIRE_DATE的月份：
　　select last_name, hire_date, extract (month from hire_date) from employees
　　where manager_id = 100;
　　TZ_OFFSET
　　显示UTC与‘US/Eastern’ （美国/ 东部）, ‘Canada/Yukon’ （加拿大/ 育空） 和 ‘Europe/London’ （欧洲/ 伦敦） 的时区偏移量
　　select tz_offset('us/eastern'),
　　tz_offset('canada/yukon'),
　　tz_offset('europe/london')
　　from dual;
　　FROM_TZ
　　显示TIMESTAMP 值 ‘2000-03-28 08:00:00’ 时区为‘Australia/North’（澳大利亚/北）， TIMESTAMP WITH TIME ZONE值。
　　select from_tz(timestamp '2000-07-12 08:00:00', 'australia/north') from dual;
　　TO_TIMESTAMP
　　显示字符串‘2007-03-06 11:00:00’ 的 TIMESTAMP 值：
　　select to_timestamp ('2007-03-06 11:00:00','YYYY-MM-DD HH:MI:SS') from dual;
　　TO_YMINTERVAL
　　显示DEPARTMENT_ID为20的员工的雇佣日期1年零2个月后的日期。
　　select hire_date,hire_date + TO_YMINTERVAL('01-02') AS
　　HIRE_DATE_YMININTERVAL
　　from employees where department_id = 20;
　　TO_DSINTERVAL
　　显示全体员工雇佣日期100天零10小时候的日期
　　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;
　　夏令时
　　4月的第一个星期日
　　– Time jumps from 01:59:59 AM to 03:00:00 AM.
　　– Values from 02:00:00 AM to 02:59:59 AM are not valid.
　　– 时间从上午01:59:59跳跃到上午03:00:00
　　– 值从上午02:00:00至02:59:59时是无效的
　　10月的最后一个星期日
　　– Time jumps from 02:00:00 AM to 01:00:01 AM.
　　– Values from 01:00:01 AM to 02:00:00 AM are ambiguous
　　because they are visited twice.
　　– 时间从上午02:00:00跳跃到上午01:00:01 。
　　– 值从上午01:00:01至上午02:00:00是不明确的，因为他们去过两次