How to calculate date diff in MySQL

Question

I have the following table:

|    ID |          HIRE_DATE |     PROMOTION_DATE |
|-------|-----------------------------------------|
| 10008 |      June, 12 1989 |   October, 15 2012 |
| 10021 |      June, 21 1999 |   October, 01 2012 |
| 10021 |      June, 21 1999 |  December, 27 2010 |
| 10029 |      June, 29 2007 |      June, 10 2013 |
| 10029 |      June, 29 2007 |      July, 01 2009 |
| 10058 | September, 12 1983 |       May, 31 2010 |
| 10100 | September, 20 1999 |  November, 14 2011 |
| 10101 |     April, 24 2006 |     March, 05 2012 |
| 10101 |     April, 24 2006 |      July, 01 2006 |
| 10122 |      July, 25 2005 |     March, 05 2012 |
| 10122 |      July, 25 2005 | September, 01 2009 |
| 10163 |      July, 06 2004 |      June, 14 2010 |
| 10163 |      July, 06 2004 |      July, 01 2007 |
| 10251 |      June, 06 1994 |     April, 01 2008 |
| 10279 | September, 01 2000 |  December, 01 2007 |
| 10320 |      July, 19 2004 | September, 19 2011 |
| 10320 |      July, 19 2004 |  December, 01 2009 |
| 10320 |      July, 19 2004 |   October, 01 2007 |

I want to calculate how long it takes for someone to get a promotion. So for the 1st person 10008, the promotion time will be the time between Oct 15, 2012 and the hire date which is Jun 12, 1989.

The 2nd person have 2 promotions, so the promotion time for the 1st promotion will be difference between Dec 27, 2010 and Jun 21, 1999; for the 2nd promotion will be difference between Oct 1, 2012 and Dec 27, 2010.

I can do this in Excel but don't know how to do this in MySQL. Excel cannot handle large data set so this become cumbersome when doing the calc in Excel.

Below is the results that I want:

|    ID |          HIRE_DATE |     PROMOTION_DATE | PROMOTION_TIME (year)
-------------------------------------------------------------------|
| 10008 |      June, 12 1989 |   October, 15 2012 |             23 |
| 10021 |      June, 21 1999 |   October, 01 2012 |              2 |
| 10021 |      June, 21 1999 |  December, 27 2010 |             12 |
| 10029 |      June, 29 2007 |      June, 10 2013 |              4 |
| 10029 |      June, 29 2007 |      July, 01 2009 |              2 |
| 10058 | September, 12 1983 |       May, 31 2010 |             27 |
| 10100 | September, 20 1999 |  November, 14 2011 |             12 |
| 10101 |     April, 24 2006 |     March, 05 2012 |              6 |
| 10101 |     April, 24 2006 |      July, 01 2006 |              0 |
| 10122 |      July, 25 2005 |     March, 05 2012 |              3 |
| 10122 |      July, 25 2005 | September, 01 2009 |              4 |
| 10163 |      July, 06 2004 |      June, 14 2010 |              3 |
| 10163 |      July, 06 2004 |      July, 01 2007 |              3 |
| 10251 |      June, 06 1994 |     April, 01 2008 |             14 |
| 10279 | September, 01 2000 |  December, 01 2007 |              7 |
| 10320 |      July, 19 2004 | September, 19 2011 |              2 |
| 10320 |      July, 19 2004 |  December, 01 2009 |              2 |
| 10320 |      July, 19 2004 |   October, 01 2007 |              3 |

Can anyone help?

Thanks,

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff — , Apr 18 '14 at 21:42

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

This is a bit painful, because MySQL does not support the lag() function. You can do it with a correlated subquery instead. So:

select id, hire_date, promotion_date,
       datediff(promotion_date, coalesce(prev_promotion_date, hire_date)) as days
from (select t.*,
             (select max(promotion_date)
              from table t2
              where t2.id = t.id and
                    t2.promotion_date < t.promotion_date
             ) as prev_promotion_date
      from table t
     ) t

If you want the value as years, I would just divide by 365.25, and let that be close enough. I you need greater accuracy, you can check out this.

edited May 23 '17 at 12:20

Community

1
1

answered Apr 18 '14 at 21:48

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, do you know where I can find a good resource on 'correlated qry'? I find this confusing. I know you used 'correlated qry' here for which I don't think about often time. Thanks! – PMa Apr 18 '14 at 22:49
@PerriMa . . . Actually, I don't. The use of correlated subqueries here is necessary because MySQL lacks window/analytic functions. My best advice would be to play with them. If you search for correlated subquery in Stack Overflow, you will find many examples. – Gordon Linoff Apr 18 '14 at 22:55

How to calculate date diff in MySQL

1 Answers1