CODEWITHSUNDEEP
mysql
PMa
PMa

Reputation: 1771

How to calculate date diff in MySQL

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,

Upvotes: 0

Views: 102

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 1

Related Questions