DEV Community

ComputerSmiths
ComputerSmiths

Posted on

Synchronize SQL database time stamps?

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)