I have a program that directly saves my MQTT messages into a mySQL database.
I save the payload, topic and create time.
I have different topics that follow a certain sequence.
I have 'topic:start, topic:phase1 with payload:A, topic:phase2 with payload:B and topic:stop'. In between I have a lot of messages with a constant feed of temperature data.
I looks like this:
| Topic | Payload | Create_time |
| 'start' | | '2016-05-17 10:00:00' |
| 'temperature' | 5 | '2016-05-17 10:00:20' |
| 'temperature' | 6 | '2016-05-17 10:01:20' |
| 'temperature' | 6 | '2016-05-17 10:02:20' |
| 'temperature' | 7 | '2016-05-17 10:03:20' |
| 'temperature' | 8 | '2016-05-17 10:04:20' |
| 'phase1' | 'A' | '2016-05-17 10:04:30' |
| 'temperature' | 8 | '2016-05-17 10:05:20' |
| 'temperature' | 9 | '2016-05-17 10:06:20' |
| 'temperature' | 10 | '2016-05-17 10:07:20' |
| 'phase2' | 'B' | '2016-05-17 10:08:30' |
| 'temperature' | 10 | '2016-05-17 10:08:50' |
| 'stop' | | '2016-05-17 10:08:30' |
... more sequences like this where A and B are sequence specific
| Date | Phase1 | Start sequence | End sequence | Phase2 |
| '2016-05-17' | 'A' | '2016-05-17 10:00:00' | '2016-05-17 10:08:30' | 'B' |
for every sequence where
- date is the date of that sequence (without time).
- the var A of that sequence
- the start create_time of that sequence
- the end create_time of that sequence
- the var B of that sequence.
you can use user defined variable to tag the sequence (by incrementing whenever you see
select date(min(Create_time)) `Date`, group_concat(if(Topic='phase1',Payload,null)) Phase1, min(Create_time) `Start sequence`, max(Create_time) `End sequence`, group_concat(if(Topic='phase2',Payload,null)) Phase2 from ( select if(Topic='start',@id:=@id+1,@id) id, a.* from ( select * from Table1 order by Create_time) a join (select @id:=0) b) a group by id;