[数据类型] Date 与 Timestamp

，- 注意

-- 尽量不在日期字段上使用函数
select * from t where to_char(dt,'yyyy') = '2011' --- 不推荐
select * from t where dt>=to_date('2011-01-01','yyyy-mm-dd') and dt<to_date('2012-01-01','yyyy-mm-dd'); --- 推荐

-- 对Timestamp字段的加减操作会导致隐式转换为Date类型
select systimestamp,systimestamp+1 from dual; --- 推荐interval

------------------------------------------------------------------------------
-- Date

--- 控制显示形式
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

--- 截断(trunc)
select trunc(dt,'yy') from t;
select trunc(dt,'mm') from t;
select trunc(dt,'dd') from t;
select trunc(dt,'hh') from t;
select trunc(dt,'mi') from t;

--- 格式化(to_char,to_date)
select to_char(dt,'yyyy-mm-dd hh24:mi:ss') from t;
select to_date(str,'yyyy-mm-dd hh24:mi:ss') from t;

--- 加减年月(add_months)
select add_months(dt,1),add_months(dt,12) from t;

--- 加减天(+1,-1)
select dt+1,dt-1 from t;

--- 加减时分秒(numtodsinterval)
select dt+numtodsinterval(1,'hour')from t;
select dt+numtodsinterval(1,'minute') from t;
select dt+numtodsinterval(1,'sencond') from t;

-- 抽取日期或时间部分
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;

--- 计算2个Date的间隔
select numtoyminterval(months_between(dt2,dt1),'month') years_months,numtodsinterval(dt2-add_months(dt1,trunc(months_between(dt2,dt1))),'day') days_hours from t;

------------------------------------------------------------------------------
-- Timestamp
--- 控制显示形式
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff6'; --- with local time zone，timestamp
alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss.ff6 tzh:tzm'; --- with time zone

--- Timestamp加减
select systimestamp+numtoyminterval(1,'year') from dual;
select systimestamp+numtoyminterval(1,'month') from dual;
select systimestamp+numtodsinterval(1,'day') from dual;
select systimestamp+numtodsinterval(1,'hour') from dual;
select systimestamp+numtodsinterval(1,'minute') from dual;
select systimestamp+numtodsinterval(1,'second') from dual;

-- 抽取日期或时间部分
select extract(year from systimestamp) from dual;
select extract(month from systimestamp) from dual;
select extract(day from systimestamp) from dual;
select extract(hour from systimestamp) from dual;
select extract(minute from systimestamp) from dual;
select extract(second from systimestamp) from dual;
select extract(timezone_hour from systimestamp) from dual;
select extract(timezone_minute from systimestamp) from dual;

--- 计算2个Timestamp的间隔
select numtoyminterval(months_between(dt2,dt1),'month') years_months,dt2-add_months(dt1,trunc(months_between(dt2,dt1)))days_hours from t;

------------------------------------------------------------------------------
-- interval

--- 创建interval year(n) to month
select numtoyminterval(5,'year')+numtoyminterval(2,'month') from dual;
select numtoyminterval(5*12+2,'month') from dual;
select to_yminterval('5-2') from dual;
select interval '5-2' year to month from dual;

--- 创建interval day(n) to second(m)
select numtodsinterval(12,'day')+numtodsinterval( 2,'hour' )+numtodsinterval( 3, 'minute' )+numtodsinterval(2.3312, 'second') from dual;
select numtodsinterval( 12.*86400+2*3600+3*60+2.3312,'second') from dual;
select to_dsinterval('12 02:03:02.3312') from dual;
select interval '12 02:03:02.3312' day to second from dual;

-- 抽取日期或时间部分 --- extract