The mysql table we work on has data in the following format:

```
    entityId    status    updated_date
    -------------------------------
      1          1        29/05/2017 12:00 
      1          2        29/05/2017 03:00
      1          3        29/05/2017 07:00
      1          4        29/05/2017 14:00
      1          5        30/05/2017 02:00
      1          6        30/05/2017 08:00
      2          1        31/05/2017 03:00
      2          2        31/05/2017 05:00
      .
      .   

```

So every entity id has 6 statuses, and every status has an update datetime. Each status has an activity attached to it.
For example 1 - Started journey
 2 - Reached first destination
 3 - Left Point A, moving towards B. etc

I need to get an output in the below format for specific entity id eg 3 and 4. I need the time for status 3 and 4 independently.

```
  entity_id   time_started_journey   time_reached_first_destination
           (update time of status 3)   (update time of status 4)
  --------------------------------------------------------------
      1       29/05/2017 7:00         29/05/2017 14:00   
      2       30/05/2017 7:00         30/05/2017 16:00

```

Later I need to calculate the total time which would be the difference of the two.

How can I achieve the desired result using mysql.

I tried using `Union` operator but cannot do it separate columns.
Also, tried using case `when operator` with the below query but failed.

```
 select distinct entityid,
 (case status when 3 then freight_update_time else 0 end) 
 as starttime,
 (case status when 4 then freight_update_time else 0 end) as endtime
 from table ;

```

Can anyone throw light on this?