SQL Lightning

[Mapantz]

Hi Mark - Busy one today..

The Ecowitt lightning detector is working an absolute treat today - picked up 12 strikes this afternoon.

I'm just in the middle if trying to generate a graph. I'm half way there with it.. As you know, I have a second instance of CMX running specifically for the GW1000 - all is good with that.

I've sort of come across a small problem.

I used the custom upload of 1 minute to go in to the 'Realtime' table, as follows:

Code: Select all

INSERT IGNORE INTO Realtime (LogDateTime,LightningDistance,StrikesToday) VALUES ('<#date format=yyyy-MM-dd> <#time format=HH:mm:ss>',<#LightningDistance>,<#LightningStrikesToday>)

It goes in absolutely fine and I can call the data OK. I then realised, that table is going to fill extremely quickly at 1 min intervals, so I would have preferred it to be a maximum of 24 hours - AKA, A retention string of '24HOUR' but that's not possible on the custom SQL command, only on the Realtime one. If I set the Realtime one as well, I get two lines being added to the table, one with the lighting data and no other station data, and one with all of the other station data and no lightning. The result is a funny looking graph.

Help!

[mcrossley]

I think you are going to have to create a new table for the lightning data.

Depending on the SQL queries you use, you may have to join the tables if you want combined data. Or create a View to join them and just query that instead of the original realtime table.