【注】急脾气者，直接跳至SQL，以下均为废话

需求如题，这种需求在工作中并不算多，但往往系统为了让客户看着更直观，在页面上就算出来两个时间相差几年、几个月、几天的数值便于用户确认。

这两个天做一个数据迁移的工作，数据量不大，但数据质量较差，里面有大量的数据清洗的工作，其中就涉及到两个时间相差几年、几个月、几天的问题。因为这个时间差是存在数据库中的(原来系统存储的是手工写的，如：一年五个月，1年6个月，壹年，5个月等等，非常不标准，但好在两个时间都还在)，所以，在清洗的时候通过两个时间计算出相差的差值。

数据库为Oracle，我们知道Oracle在时间处理上有很多函数，但没有一个函数能返回我们想要的信息。其实Oracle针对这个问题专门有几个数据类型

INTERVAL YEAR[ (years_precision)] TO MONTH

INTERVAL DAY[(days_precision)] TO SECOND[( seconds_precision)]

生产中这种数据类型用的很少，就算有类似的需求，就算这个数据类型比较合适，也会因为不常用而改用其他方式处理。

测试：

Default

## 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

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

## Oracle11g HR Schema下的job_history表

SQL>select *fromjob_history;

EMPLOYEE_IDSTART_DATEEND_DATEJOB_IDDEPARTMENT_ID

--------------------------------------------------------

1022001/1/132006/7/24IT_PROG60

1011997/9/212001/10/27AC_ACCOUNT110

1012001/10/282005/3/15AC_MGR110

2012004/2/172007/12/19MK_REP20

1142006/3/242007/12/31ST_CLERK50

1222007/1/12007/12/31ST_CLERK50

2001995/9/172001/6/17AD_ASST90

1762006/3/242006/12/31SA_REP80

1762007/1/12007/12/31SA_MAN80

2002002/7/12006/12/31AC_ACCOUNT90

10rowsselected

SQL>

处理起来也不算复杂，大致思路如下：

1：通过months_between获取两个日期相差的月份数并trunc取整

2：总的相差的月份数除以12，再trunc取整，返回的即为相差的年数

3：通过两个时间相减可以返回天数，【结束时间】-(【开始时间】+【相差的月份数】)=【相差的天数】

4：通过【总的月份数】-(【相差年数】*12)]=【月份数】

Default

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>

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>selectemployee_id,y,(mc-y *12)asm,d,start_date,end_date

2from(selectemployee_id,

3end_date-add_months(start_date,mc)asd,

4mc,

5trunc(case

6whenmc>=12then

7mc/12

8else

90

10end)asy,

11start_date,

12end_date

13from(selectemployee_id,

14trunc(months_between(end_date,start_date))asmc,

15start_date,

16end_date

17fromjob_history));

EMPLOYEE_IDYMDSTART_DATEEND_DATE

---------------------------------------------------------------

10256112001/1/132006/7/24

1014161997/9/212001/10/27

10134152001/10/282005/3/15

20131022004/2/172007/12/19

1141972006/3/242007/12/31

122011302007/1/12007/12/31

2005901995/9/172001/6/17

1760972006/3/242006/12/31

176011302007/1/12007/12/31

20045302002/7/12006/12/31

10rowsselected

SQL>

利用with子句看着可能会更清楚一些

Default

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>

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>witht1as

2(selectemployee_id,

3trunc(months_between(end_date,start_date))asmc,

4start_date,

5end_date

6fromjob_history),

7t2as

8(selectemployee_id,

9end_date-add_months(start_date,mc)asd,

10mc,

11trunc(case

12whenmc>=12then

13mc/12

14else

150

16end)asy,

17start_date,

18end_date

19fromt1),

20t3as

21(selectemployee_id,y,(mc-y *12)asm,d,start_date,end_datefromt2)

22select *fromt3;

EMPLOYEE_IDYMDSTART_DATEEND_DATE

---------------------------------------------------------------

10256112001/1/132006/7/24

1014161997/9/212001/10/27

10134152001/10/282005/3/15

20131022004/2/172007/12/19

1141972006/3/242007/12/31

122011302007/1/12007/12/31

2005901995/9/172001/6/17

1760972006/3/242006/12/31

176011302007/1/12007/12/31

20045302002/7/12006/12/31

10rowsselected

SQL>