【ORACLE】ORACLE时区总结

我不是女神ヾ 2023-07-24 13:06 10阅读 0赞

--设置会话时区
    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小时
    --夏令时