Hi All,
I've got several SQL databases from machines logging sunlight data that I'd like to synchronize for solar power studies. One example is:
mysql> select ComputerTime, Pac, IntSolIrr from SMA where ComputerTime >= (unix_timestamp(now())-(60*10)) order by ComputerTime;
+--------------+------+-----------+
| ComputerTime | Pac | IntSolIrr |
+--------------+------+-----------+
| 1552395307 | 3738 | 557 |
| 1552395366 | 3869 | 583 |
| 1552395425 | 3813 | 578 |
| 1552395487 | 3874 | 586 |
| 1552395546 | 3853 | 587 |
| 1552395607 | 3917 | 598 |
| 1552395666 | 3998 | 611 |
| 1552395728 | 4054 | 621 |
| 1552395785 | 4114 | 633 |
| 1552395846 | 4149 | 634 |
+--------------+------+-----------+
10 rows in set (0.84 sec)
While another nearby one is:
MariaDB [Weather]> select Epoch,WF$UDP$obs_sky$obs$SolarRadiation_Wpm2 from WeatherFlow where Epoch >= (unix_timestamp(now())-(60*10)) order by Epoch;
+------------+----------------------------------------+
| Epoch | WF$UDP$obs_sky$obs$SolarRadiation_Wpm2 |
+------------+----------------------------------------+
| 1552395304 | 433 |
| 1552395364 | 433 |
| 1552395424 | 445 |
| 1552395484 | 442 |
| 1552395544 | 448 |
| 1552395604 | 450 |
| 1552395664 | 457 |
| 1552395724 | 467 |
| 1552395784 | 476 |
| 1552395844 | 483 |
+------------+----------------------------------------+
10 rows in set (0.82 sec)
As you can see, they are logging on one-minute intervals, but can be off from each other by a few seconds.
Is there an easy way in SQL to get these measurements synchronized? My brute force approach would probably be to look for the closest one-minute timestamp by subtracting and then ordering by delta and taking the last one, but for one-minute intervals over several year that's going to take a long time... 8*}
Alternately, I can read them into separate arrays and do the math outside SQL, but if there's an easy way to say "to the closest Epoch/60" or something, that might be easier...
Many thanks in advance for any pointers to real programming techniques as eventually I'll have a dozen or more datasets to sync up...
Top comments (0)