【ORACLE】ORACLE时区总结

--设置会话时区
alter session set time_zone = '+10:00';
--设置会话时间格式
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select DBTIMEZONE，sessiontimezone, current_date,current_timestamp,localtimestamp from dual;
--current_date就是当前的事件
--current_timestamp加了时区
--localtimestamp没有加时区
--sessiontimezone 当前会话的时区
--DBTIMEZONE 数据库时区

--命名区域值
select table_name from dict where table_name like '%TIMEZONE%';
select * from GV$TIMEZONE_NAMES

--TIMESTAMP with local time zone
create table web_shop(
f_time timestamp with time zone,
e_time timestamp with local time zone
)
insert into web_shop values(sysdate,sysdate+2)
--这个时候如果改变了 time_zone 则e_time就会根据 time_zone修改的值来做出改变，而f_time一旦设置好就不会发生改变
--这是timestamp with time zone 和 timestamp with local time zone的区别
select * from web_shop

--interval year to month
--间隔年到月
create table warranty(
prod_id number, warranty_time interval year(3) to month--表示年可以放3位
)
insert into warranty values(123, '1-2')
insert into warranty values(125,interval '10' year);
insert into warranty values(125,interval '2-5' year to month);
insert into warranty values(125,interval '6' month);
select * from warranty
--间隔天到秒
create table lab(
exp_id number, test_time interval day(2) to second--表示年可以放3位
)
insert into lab values(100012, '90 00:00:00');
insert into lab values(56060, interval '6 03:30:16' day to second);
insert into lab values(56060, interval '1' day);
insert into lab values(56060, interval '1' minute);
insert into lab values(56060, interval '1' second);
insert into lab values(56060, interval '0 00:00:10' day to second);
select * from lab
--时间抽取 EXTRACT
--返回的是一个数字类型  ps：数字类型是右对齐，字符串类型是左对齐
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
--时间的绝对位移  TZ_OFFSET
select TZ_OFFSET('asia/chongqing') from dual;
select TZ_OFFSET('europe/london') from dual;
select TZ_OFFSET('canada/yukon') from dual;
--字符串转换为时间戳的形式 FROM_TZ
select FROM_TZ(timestamp'2000-07-12 08:00:00','europe/london') from dual;
--to_timestamp
select to_timestamp('2007-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss') from dual;
--numtoyminterval 加年或月的时间
select sysdate+1 ,sysdate+ numtoyminterval(1，'year') from dual
select sysdate+1 ,sysdate+ numtoyminterval(1，'month') from dual
----numtodsinterval 加天或秒的时间
select sysdate+1 ,sysdate+ numtodsinterval(1，'day') from dual
select sysdate+1 ,sysdate+ numtodsinterval(1，'second') from dual
--to_yminterval 年到月
select sysdate+1 ,sysdate+ to_yminterval('1-3') from dual  --加上一年零三个月
--to_dsinterval  天到秒
select sysdate+1 ,sysdate+ to_dsinterval('100 23:00:00') from dual  --加上100天 23小时
--夏令时