【Oracle】【SQL】日期差返回几年、几个月、几天
xixuefeng
Oracle, SQL&Function
2018-03-26 9:28:49
2,539 次浏览
【Oracle】【SQL】日期差返回几年、几个月、几天已关闭评论
【注】急脾气者，直接跳至SQL，以下均为废话
需求如题，这种需求在工作中并不算多，但往往系统为了让客户看着更直观，在页面上就算出来两个时间相差几年、几个月、几天的数值便于用户确认。
这两个天做一个数据迁移的工作，数据量不大，但数据质量较差，里面有大量的数据清洗的工作，其中就涉及到两个时间相差几年、几个月、几天的问题。因为这个时间差是存在数据库中的（原来系统存储的是手工写的，如：一年五个月，1年6个月，壹年，5个月等等，非常不标准，但好在两个时间都还在），所以，在清洗的时候通过两个时间计算出相差的差值。
数据库为Oracle，我们知道Oracle在时间处理上有很多函数，但没有一个函数能返回我们想要的信息。其实Oracle针对这个问题专门有几个数据类型
INTERVAL YEAR[ (years_precision)] TO MONTH
INTERVAL DAY[(days_precision)] TO SECOND[( seconds_precision)]
生产中这种数据类型用的很少，就算有类似的需求，就算这个数据类型比较合适，也会因为不常用而改用其他方式处理。
测试：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
## Oracle11g HR Schema下的job_history表 SQL> select * from job_history; EMPLOYEE_ID START_DATE END_DATE JOB_ID DEPARTMENT_ID ----------- ----------- ----------- ---------- ------------- 102 2001/1/13 2006/7/24 IT_PROG 60 101 1997/9/21 2001/10/27 AC_ACCOUNT 110 101 2001/10/28 2005/3/15 AC_MGR 110 201 2004/2/17 2007/12/19 MK_REP 20 114 2006/3/24 2007/12/31 ST_CLERK 50 122 2007/1/1 2007/12/31 ST_CLERK 50 200 1995/9/17 2001/6/17 AD_ASST 90 176 2006/3/24 2006/12/31 SA_REP 80 176 2007/1/1 2007/12/31 SA_MAN 80 200 2002/7/1 2006/12/31 AC_ACCOUNT 90 10 rows selected SQL> |
处理起来也不算复杂，大致思路如下：
1：通过months_between获取两个日期相差的月份数并trunc取整
2：总的相差的月份数除以12，再trunc取整，返回的即为相差的年数
3：通过两个时间相减可以返回天数，【结束时间】-（【开始时间】+【相差的月份数】）=【相差的天数】
4：通过【总的月份数】-(【相差年数】*12)]=【月份数】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SQL> select employee_id, y, (mc - y * 12) as m, d, start_date, end_date 2 from (select employee_id, 3 end_date - add_months(start_date, mc) as d, 4 mc, 5 trunc(case 6 when mc >= 12 then 7 mc / 12 8 else 9 0 10 end) as y, 11 start_date, 12 end_date 13 from (select employee_id, 14 trunc(months_between(end_date, start_date)) as mc, 15 start_date, 16 end_date 17 from job_history)); EMPLOYEE_ID Y M D START_DATE END_DATE ----------- ---------- ---------- ---------- ----------- ----------- 102 5 6 11 2001/1/13 2006/7/24 101 4 1 6 1997/9/21 2001/10/27 101 3 4 15 2001/10/28 2005/3/15 201 3 10 2 2004/2/17 2007/12/19 114 1 9 7 2006/3/24 2007/12/31 122 0 11 30 2007/1/1 2007/12/31 200 5 9 0 1995/9/17 2001/6/17 176 0 9 7 2006/3/24 2006/12/31 176 0 11 30 2007/1/1 2007/12/31 200 4 5 30 2002/7/1 2006/12/31 10 rows selected SQL> |
利用with子句看着可能会更清楚一些
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SQL> with t1 as 2 (select employee_id, 3 trunc(months_between(end_date, start_date)) as mc, 4 start_date, 5 end_date 6 from job_history), 7 t2 as 8 (select employee_id, 9 end_date - add_months(start_date, mc) as d, 10 mc, 11 trunc(case 12 when mc >= 12 then 13 mc / 12 14 else 15 0 16 end) as y, 17 start_date, 18 end_date 19 from t1), 20 t3 as 21 (select employee_id, y, (mc - y * 12) as m, d, start_date, end_date from t2) 22 select * from t3; EMPLOYEE_ID Y M D START_DATE END_DATE ----------- ---------- ---------- ---------- ----------- ----------- 102 5 6 11 2001/1/13 2006/7/24 101 4 1 6 1997/9/21 2001/10/27 101 3 4 15 2001/10/28 2005/3/15 201 3 10 2 2004/2/17 2007/12/19 114 1 9 7 2006/3/24 2007/12/31 122 0 11 30 2007/1/1 2007/12/31 200 5 9 0 1995/9/17 2001/6/17 176 0 9 7 2006/3/24 2006/12/31 176 0 11 30 2007/1/1 2007/12/31 200 4 5 30 2002/7/1 2006/12/31 10 rows selected SQL> |