CODEWITHSUNDEEP
mysqlsql
Deav
Deav

Reputation: 13

MySQL select min, max on timeline

Is it possible to get min and max timestamp based on timeline? I will explain it on car parking example.

+---------------------+------+--------+-------+------------+
| ts                  | pos  | posidx | car   | carowner   |
+---------------------+------+--------+-------+------------+
| 2016-02-16 20:15:02 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-16 20:30:02 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-16 20:35:01 | Lev2 |      2 | Volvo | Mr Johnson |
| 2016-02-16 22:20:01 | Lev2 |      2 | Volvo | Mr Johnson |
| 2016-02-16 22:25:02 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-16 22:30:01 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-17 00:30:01 | Lev1 |      3 | GM    | Mr Johnson |
| 2016-02-17 00:35:02 | Lev1 |      3 | GM    | Mr Johnson |

Let's assume that pos is a parking level, posidx is a parking place number. I need report for carowner and each his car in timeline, which should look like this:

+---------------------+---------------------+------+--------+-------+------------+
| min(ts)             | max(ts)             | pos  | posidx | car   | carowner   |
+---------------------+---------------------+------+--------+-------+------------+
| 2016-02-16 20:15:02 | 2016-02-16 20:30:01 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-16 20:35:01 | 2016-02-16 22:20:01 | Lev2 |      2 | Volvo | Mr Johnson |
| 2016-02-16 22:25:02 | 2016-02-17 00:25:01 | Lev2 |      3 | Volvo | Mr Johnson |
| 2016-02-17 00:30:01 | 2016-02-17 00:35:02 | Lev1 |      3 | GM    | Mr Johnson |

I've tried with:

SELECT min(ts), max(ts), pos, posidx, car, carowner
from parking
where carowner = 'Mr Johnson'
group by pos, posidx, car

But because of posidx grouping it doesn't show 3rd line of above report and show its max ts in first line.

Can you help me?

Upvotes: 1

Views: 80

Answers (2)

Khalid Amin
Khalid Amin

Reputation: 902

The rows 1,2,5,6 are exactly the same except the ts column. The query will display only MIN and MAX values out of these 4 rows. Logically, its impossible to tell which EXIT row corresponds to which ENTER row. To successfully retrieve your desired result, there should be another column to identify pairs of timeline rows.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You need to assign a grouping identifier. One way to think of this is that it is a counter that increments when certain values change. With that notion, you can use a variable to assign the grouping and then aggregation:

select min(ts), max(ts), pos, posidx, car, carowner
from (select t.*, 
             (@grp := if(@pcc = concat_ws(':', posidx, car, carowner), @grp,
                         if(@pcc := concat_ws(':', posidx, car, carowner), @grp+1, @grp+1)
                        )
             ) grp
      from t cross join
           (select @pcc := '', @grp := 0) params
      order by ts
     ) t
group by pos, posidx, car, carowner, grp;

Upvotes: 2

Related Questions