MySQL date difference between column3 of first row and column2 of second row

Question

I searched for the above topic and only getting query in Oracle which uses certain keywords specific to oracle.

+----------+------------+--------------------+
| Agent_id | valid_from | last_modified_date |
+----------+------------+--------------------+
| 13002    | 2010-12-25 | 2011-01-03         |
| 13002    | 2011-01-03 | 2011-08-25         |
| 13002    | 2011-08-26 | 2012-12-30         |
| 13002    | 2013-01-01 | 2013-01-01         |
| 12110    | 2014-02-27 | 2014-03-03         |
| 12110    | 2014-03-25 | 2014-12-25         |
+----------+------------+--------------------+

I have the above table values and want to retrieve difference between last_modified_date of 1st row and valid_from date of 2nd row and likewise for the same agent(agent id here).

Result table:

+----------+------------+--------------------+-----------+
| Agent_id | valid_from | last_modified_date | datediff  |
+----------+------------+--------------------+-----------+
| 13002    | 2010-12-25 | 2011-01-03         |         0 |
| 13002    | 2011-01-03 | 2011-08-25         |         0 |
| 13002    | 2011-08-26 | 2012-12-30         |         1 |
| 13002    | 2013-01-01 | 2013-01-01         |         1 | 
| 12110    | 2014-02-27 | 2014-03-03         |         0 |
| 12110    | 2014-03-25 | 2014-12-25         |        22 |
+----------+------------+--------------------+-----------+

If there is no date for comparison on first row diff should be 0. These are set of dates where the status gets changed from Y to D and to find when the agent is without any activity.

please help!!

Given that rows in an RDBMS table have no order, which one is first, and which one is second (and why?) — Strawberry, Apr 21 '14 at 11:10

Sharikov Vladislav · Answer 1 · 2014-04-21T11:08:09.437

1

Use DATEDIFF function.

Example for MySQL:

SELECT 
    DATEDIFF(valid_from,last_modified_date) AS 'days' 
FROM 
    table

Working example on SQLFiddle.

This will return difference in days. Source

Same for SQL Server 2005-2012:

SELECT 
    DATEDIFF(day,valid_from,last_modified_date ) AS 'days' 
FROM 
    table

This will return difference in days. Source

edited Apr 21 '14 at 11:08

answered Apr 21 '14 at 10:45

Sharikov Vladislav

7,049
9
50
87

mleko · Answer 2 · 2014-04-21T10:55:39.393

0

Difference in days.

SELECT *,MIN(COALESCE(DATEDIFF(t1.valid_from, t2.last_modified_date),0)) 
FROM agents t1
LEFT JOIN agents t2 ON t1.agent_id=t2.agent_id AND t1.valid_from >= t2.last_modified_date
GROUP BY t1.agent_id, t1.valid_from

http://sqlfiddle.com/#!2/8b6ee/4

edited Apr 21 '14 at 10:55

answered Apr 21 '14 at 10:44

mleko

11,650
6
50
71

Hi mleko thanks!
The query works except for one date it showing incorrect values as we are using '>=' when only '>' is used it works correctly.Thanks for the immediate help. But the query is taking long time.how can i fine tune? – user3555847 Apr 21 '14 at 12:36
@user3555847 `>=` was used on purpose check this http://sqlfiddle.com/#!2/28763/2 . Can you create fiddle where it gives bad result. I would add another column with `PRIMARY KEY` and add another condition in `JOIN` `t1.pk_id>t2.pk_id` Adding `INDEX` on `agent_id` might help a bit with performance – mleko Apr 21 '14 at 12:46
Thanks mleko. when last_modified_date and valid from are same then the minimum date is 0 pl see fiddle link above – user3555847 Apr 25 '14 at 11:42
@user3555847 right, as I said. Some unique key could help http://sqlfiddle.com/#!2/468e3/1 – mleko Apr 26 '14 at 13:00

Gordon Linoff · Answer 3 · 2014-04-21T13:42:57.420

0

The easiest way to do this in MySQL is using variables:

select t.*
from (select t.*,
             if(agent_id = @agent_id, datediff(valid_from, @last_modified_date), 0) as datediff,
             @last_modified_date := last_modified_date,
             @agent_id := agent_id
      from table t cross join
           (select @agent_id := 0, @last_modified_date := 0) const
      order by agent_id, valid_from
     ) t;

You can also calculate the previous date using correlated subqueries.

By the way, those keywords that you would use in Oracle are not Oracle-specific. They are ANSI standard functionality that MySQL does not support.

edited Apr 21 '14 at 13:42

answered Apr 21 '14 at 10:46

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, Thank you!
I am getting the following error.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@last_modified_date := date_modified, @agent_id := agent_id ' at line 4
– user3555847 Apr 21 '14 at 12:34

score 0 · Answer 4 · answered Apr 21 '14 at 12:33

Try this query. Change the order by clause accordingly.

I have assumed the order based on your result.

   select  agent_id,
    max(valid_from) as valid_from,max(last_modified_date) as last_modified_date,
    ifnull(datediff(max(valid_from),max(last_modified_date)),0)as difference

    from
    (

    select @a:=@a+1,
    case  when (@a+1)%2 = 0 then @b:=@a-2  else @b end as b  , agent_id,
    case when @a%2=0 then valid_from else 0 end as valid_from,
    case when @a%2<>0 then last_modified_date else 0 end as last_modified_date
    from table a  ,(select @a:=0,@b:=0) b
    order by agent_id desc ,valid_from

    ) a
    group by agent_id,b

score 0 · Answer 5 · answered Apr 21 '14 at 13:57

just change the order of fields in result a little bit

mysqli_multi_query('
    set @i='';
    select 
        Agent_id, if(@i='', 0,datediff(valid_from,@i)) as datediff, valid_from, (@i:=last_modified_date) as last_modified_date
    from 
        your_table'
);

MySQL date difference between column3 of first row and column2 of second row

5 Answers5