Oracle日期时间处理
日期函数
1. 常用日期函数
(1) add_months(date, n) : 'n' 个月前，'-n' 个月后
(2) months_between(date1, date2) : 月份差 'date1 - date2'，存在小数
(3) last_day(date)：返回当前日期在该月份的最后一天的日期
2. 时区（了解即可）
(1) select dbtimezone from dual;
(2) select sessiontimezone from dual;
(3) select TZ_OFFSET('PST') from dual;
(4) select * from v$timezone_names; -- 时区名
(5) select new_time(current_date,'PST','EST') from dual; -- 转换示例：
select sysdate 当前时间,
trunc(sysdate) 当前日期, -- 返回 date 类型, 无时分秒
trunc(sysdate, 'hh24') -- 精确到小时 yyyy-mm-dd HH:00:00
trunc(sysdate, 'Q') 当前季度第一天,
trunc(sysdate, 'W') 当前周第一天, -- 周日
add_months(sysdate, 2) 两个月后,
add_months(sysdate, -1) 两个月前,
sysdate + 3 三天后,
sysdate + 10 / (24 * 60 * 10) 十秒后,
months_between(date '2020-10-01', date '2020-01-01') 日期月份差,
next_day(sysdate, 2) 下周第二天, -- 下周一
last_day(sysdate) 本月最后一天,
round(sysdate) 舍入到最接近的日期, -- 精确到天
round(sysdate, 'YEAR') 舍入到最接近的年,
extract(year from sysdate) 当前年, -- 返回 number 类型， 同理，月：month，日：day
extract(hour from timestamp '2020-11-17 11:22:33') 当前小时, -- 同理，分：minute，秒：second
least(date '2020-01-01', date '2020-10-01') 日期列表最早的日期,
greatest(date '2020-01-01', date '2020-10-01') 日期列表最晚的日期
from dual;日期格式
1. 常用日期格式 -- 不区分大小写
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') 当前时间 from dual;
如: 2020-11-17 20:30:40
(1) YYYY-MM-DD '年'-'月'-'日' -- 四位数-两位数-两位数
(2) HH24:MI:SS '时':'分':'秒' -- HH12、HH : 均为 12 小时制
2. 特别说明
(1) 一周的第一天是 "周日"
3. 参数设置
(1) select * from nls_session_parameters; -- nls_date_language： simplified chinese
(2) alter session set nls_date_language = 'AMERICAN'; -- 设置英文语言格式示例：
select sysdate 当前时间,
to_char(sysdate, 'YYYY') 四位年, -- 时、分、秒 同理
to_char(sysdate, 'YY') 两位年, -- 同理还有1位和3位，用的较少（容易混淆）
to_char(sysdate, 'MM') 月份,
to_char(sysdate, 'MONTH') 月份带月份名, -- 若是英文语言格式，显示 november
to_char(sysdate, 'MON') 月份带月份名简称, -- 若是英文语言格式，显示 nov （11月）
to_char(sysdate, 'RM') 月份罗马数字,
to_char(sysdate, 'DDD') 当年第n天,
to_char(sysdate, 'DD') 当月第n天,
to_char(sysdate, 'D') 当周第n天, -- 周日是第一天。day 的简称
to_char(sysdate, 'DY') 星期几,
to_char(sysdate, 'Q') 当年第n季度,
to_char(sysdate, 'WW') 当年第n周,
to_char(sysdate, 'W') 本月第n周,
to_char(sysdate, 'HH24') "24小时制",
to_char(sysdate, 'HH12') "12小时制" -- 默认 hh = hh12
from dual;类型转换
基础数据准备：
create table date_conversion_test (
date_char varchar2(20),
date_date date,
date_timestamp timestamp
);
insert into date_conversion_test
(date_char, date_date, date_timestamp)
values
(to_char(sysdate, 'YYYY-MM-DD'), sysdate, sysdate);示例：
select to_char(t.date_date, 'YYYY-MM-DD') "date -> char",
to_char(t.date_timestamp, 'YYYY-MM-DD') "timestamp -> char",
to_date(t.date_char, 'YYYY-MM-DD') "char -> date",
to_timestamp(t.date_char, 'YYYY-MM-DD') "char -> date"
from date_conversion_test t;项目实际案例
加减时间（年月日时分秒）
Oracle中日期进行加减可以使用多种方式，以下介绍三种：
- 一种是针对天的操作，适用于对日，时，分，秒的操作，
- 一种是对月的操作，适用于月，年的操作，
- 一种是使用INTERVAL函数，适用于年，月，日，时，分，秒的操作
直接加减数字
select sysdate "当前时间",sysdate+1 "当前时间加一天" from dual; -- 加一天
SELECT
SYSDATE "当前时间",
SYSDATE + 1 "加一天",
SYSDATE + (1 / 24) "加一小时",
SYSDATE + (1 / 24 / 60) "加一分钟",
SYSDATE + (1 / 24 / 60 / 60) "加一秒钟",
SYSDATE - 1 "减一天"
FROM
dual;查询结果如下：
通过ADD_MONTHS()函数
select sysdate "当前时间",ADD_MONTHS(sysdate, 1) "加一月" from dual; -- 加一个月
SELECT
SYSDATE "当前时间",
ADD_MONTHS (SYSDATE, 1) "加一月",
ADD_MONTHS (SYSDATE, - 1) "减一月",
ADD_MONTHS (SYSDATE, 1 * 12) "加一年",
ADD_MONTHS (SYSDATE, - 1 * 12) "减一年"
FROM
dual;查询结果如下：
使用INTERVAL函数
语法：INTERVAL '时间差数值' { YEAR | MONTH | DAY | HOUR | MINUTE | SECODE} (精度数值)
得出的是相应类型的时间差，注意精度数值范围是1-9，不写默认值是2，精度不匹配时间差数值会报错
例如：当前时间加1,11,111年
SELECT
SYSDATE "当前时间",
SYSDATE + INTERVAL '1' YEAR "加1年", -- 精度数值不写时是默认两位，此处可以写精度为1-9
SYSDATE + INTERVAL '11' YEAR "加11年", -- 此处可以不写，可以是1-9
SYSDATE + INTERVAL '11' YEAR(2) "加11年", -- 可以写2-9
SYSDATE + INTERVAL '111' YEAR(3) "加111年", -- 此处必须写精度，精度值3-9
SYSDATE + INTERVAL '111' YEAR (9) "加111年" -- 精度值可以大于当前精度，不能小于当前数值精度，不能超过9
FROM
dual;查询结果：
SELECT
SYSDATE "当前时间",
SYSDATE + INTERVAL '1' YEAR "加一年",
SYSDATE + INTERVAL '-1' YEAR "减一年",
SYSDATE + INTERVAL '1' MONTH "加一月",
SYSDATE + INTERVAL '1' DAY "加一天",
SYSDATE + INTERVAL '1' HOUR "加一小时",
SYSDATE + INTERVAL '1' MINUTE "加一分钟",
SYSDATE + INTERVAL '1' SECOND "加一秒"
FROM
dual;结果如下：
精度的使用同上，当然oracle数据库对于时间大小也是有限制的，年份范围是 -4713 和 +9999 之间, 且不为 0
求时间为星期几或者几月份或多少年
select to_char(sysdate,'D') from dual; --这周的第几天；每周的第一天为星期天，星期一为第二天
--某天星期几 同样可以求多少年(year)多少月(month)
select to_char(to_date('2021-04-08','yyyy-mm-dd'),'day') from dual; --20210408为星期四
--输出英文。某天星期几 同样可以求多少年多少月
select to_char(to_date('2021-04-08','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = AMERICAN') from dual; 求两个日期之间的月份
-- 求两个日期之间的月份（如果日期存在差值，则会有很长的小数,使用floor函数可以向下取整。）
-- 结果0，不满一个月
SELECT floor(MONTHS_BETWEEN(to_date('2021-05-01','yyyy-mm-dd'), to_date('2021-04-02','yyyy-mm-dd'))) as month from dual;
-- 求两个日期之间的月份（如果日期存在差值，则会有很长的小数,使用round函数可以四舍五入。）
-- 结果1，过了一半就算一个月。根据需求选择
SELECT ROUND(MONTHS_BETWEEN(to_date('2021-05-01','yyyy-mm-dd'), to_date('2021-04-02','yyyy-mm-dd')),0) as month from dual;
-- 扩展
-- 求人员的年龄
-- 月份相减计算年龄（推荐）
SELECT TRUNC(MONTHS_BETWEEN(to_date('2021-04-01','yyyy-mm-dd'), to_date('1997-04-02','yyyy-mm-dd'))/12,0) as age from dual; 求两个日期的天数
--两个日期间的天数差，同理除365获得年，除30得到月。乘于24得到小时差
select floor(TO_NUMBER(sysdate - to_date('20200405','yyyymmdd'))) from dual;计算年龄
-- 月份相减计算年龄（推荐）
SELECT TRUNC(MONTHS_BETWEEN(to_date('2021-04-01','yyyy-mm-dd'), to_date('1997-04-02','yyyy-mm-dd'))/12,0) as age from dual;
--年份相减计算年龄
SELECT TO_CHAR(to_date('2021-04-01','yyyy-mm-dd'),'YYYY') - TO_CHAR(to_date('1997-04-02','yyyy-mm-dd'),'YYYY') as age from dual; 计算分钟差
-- 求两个时间的分钟差 什么都不乘代表天数，不能四舍五入
SELECT floor(TO_NUMBER(SYSDATE - TO_DATE('2021-04-15 14:05:20', 'YYYY-MM-DD hh24:mi:ss')) * 24 *60) as time from dual;计算一个月中，星期一至星期五的天数
-- 求一个月中除开星期六和星期天的天数
-- 思路：获取累加数，通过日期累加rownum在tochar取星期几判断即可
-- 注意：星期日为一周的开始即是1，星期一为2以此类推
select count(*)
from (select rownum-1 rnum from all_objects where rownum <= to_date('2002-04-30','yyyy-mm-dd') - to_date('2002-04-01','yyyy-mm-dd')+1)
where to_char(to_date('2021-04-01','yyyy-mm-dd')+rnum,'D') not in ('1','7');求今天是这个星期的第几天，这个月第几天，今年第几天
select to_char(sysdate,'D') from dual; --这个星期的第几天
select to_char(sysdate,'DD') from dual; --这个月的第几天
select to_char(sysdate,'DDD') from dual; --今年的第几天求不同时间格式的第一天
-- 通过trunc函数截取标准不同，获取不同时间的开始
select TRUNC(sysdate,'DD') from dual;-- 今天的开始
select TRUNC(sysdate,'iw') from dual;-- 这周的第一天
select TRUNC(sysdate,'MM') from dual;-- 这个月的第一天
select TRUNC(sysdate,'q') from dual;-- 这个季度的第一天
select TRUNC(sysdate,'YYYY') from dual; --今年第一天时间格式化
-- 时间格式化为字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;
-- 返回的是自然周于本年的周数，一年周数最少不低于52
select to_char(SYSDATE,'iw') from dual;
--返回季度
select to_char(sysdate,'q') from dual;
--当前时间的年
select to_char(sysdate,'yyyy') from dual;
--当前时间的月
select to_char(sysdate,'mm') from dual;
--当前时间的日
select to_char(sysdate,'dd') from dual;
--当前时间的时
select to_char(sysdate,'hh24') from dual;
--当前时间的分
select to_char(sysdate,'mi') from dual;
--当前时间的秒
select to_char(sysdate,'ss') from dual; 求两个时间的天数、小时、分钟和秒数差
-- 求两个时间的天数、小时差、分钟差和秒数差
SELECT
time,
floor(time) day,
case when time < 1 then TRUNC(time*24) when time > 1 then MOD(TRUNC(time*24), 24) else 0 END as hour,
TRUNC(time*24*60 - 60*TRUNC(time*24)) minute, -- 取整再乘60分再相减就可以得出小数部分的分钟数
TRUNC(time*24*60*60 - 60*TRUNC(time*24*60)) second
FROM
(SELECT TO_NUMBER(SYSDATE - TO_DATE('2021-04-15 14:05:20', 'YYYY-MM-DD hh24:mi:ss')) as time from dual) 求当月天数
-- extract(day from date) day为时间关键字，date为日期
-- 使用extract函数获取当月第一天到date的天数
SELECT extract(day from last_day(sysdate)) FROM dual;
