Consultas personalizadas de WSPRnet: TX y RX

Si se quieren ver los registros completos (SELECT *) de un intervalo, por ejemplo desde las 00h00 a las 01h00 en las que EA5JTT recibe tramas o las suyas son recibidas por otras estaciones haríamos:

• SELECT * FROM wspr.rx WHERE (rx_sign='EA5JTT' OR tx_sign='EA5JTT') AND time > '2025-07-09 00:00:00' AND time < '2025-07-09 01:00:00' ORDER BY time;

• SELECT * FROM wspr.rx WHERE (rx_sign='EA5JTT' OR tx_sign='EA5JTT') AND (rx_sign='LA1ITS' OR tx_sign='LA1ITS') AND time > '2025-07-09 00:00:00' AND time < '2025-07-09 01:00:00' ORDER BY time;

Un poco mas elaborado es la siguiente Query que intenta encontrar las parejas o QSO en WSPR establecidos en el dia 9 de las 00 a las 01h con un intervalo de 10 minutos (da error por tiempo, pero se puede afinar)

• SELECT  tx.time AS tx_time, tx.tx_sign AS tx_sender,  tx.rx_sign AS tx_receiver, rx.time AS rx_time, rx.tx_sign AS rx_sender, rx.rx_sign AS rx_receiver FROM wspr.rx tx INNER JOIN wspr.rx rx   ON tx.tx_sign = rx.rx_sign   AND tx.rx_sign = rx.tx_sign AND abs(toUnixTimestamp(tx.time) - toUnixTimestamp(rx.time)) <= 600 WHERE   tx.time > '2025-07-09 00:00:00'  AND tx.time < '2025-07-09 01:00:00' AND rx.time >= '2025-07-09 00:00:00' AND rx.time < '2025-07-09 01:00:00';

Concretando para nuestra estación EA5JTT para un log o generara WSL's

• SELECT tx.id AS id_TX, tx.time AS time_TX, tx.band AS band_TX, tx.frequency AS f_TX_Hz, tx.tx_sign AS Callsign_TX, tx.power AS power_TX, tx.rx_sign AS Callsign_RX, tx.snr AS SNR_RX, rx.id AS id_rx, rx.time AS time_rx, rx.band AS band_rx, rx.frequency AS f_rx_Hz, rx.tx_sign AS Callsign_tx , rx.power AS power_rx,  rx.rx_sign AS Callsign_rx ,  tx.snr AS SNR_rx FROM wspr.rx tx INNER JOIN wspr.rx rx  ON tx.tx_sign = rx.rx_sign  AND tx.rx_sign = rx.tx_sign AND abs(toUnixTimestamp(tx.time) - toUnixTimestamp(rx.time)) <= 300 WHERE    tx.time > '2025-07-09'    AND tx.time < '2025-07-10'   AND rx.time >= '2025-07-09' AND rx.time < '2025-07-10' AND tx.tx_sign ='EA5JTT';

Si queremos excluir algunos indicativos que ya tenemos informados o con QSL podemos añadir una lista con ellos "tx.rx_sign NOT IN ('G4LEN','DF1VB' )"

• SELECT tx.id AS id_TX, tx.time AS time_TX, tx.band AS band_TX, tx.frequency AS f_TX_Hz, tx.tx_sign AS Callsign_TX, tx.power AS power_TX, tx.rx_sign AS Callsign_RX, tx.snr AS SNR_RX, rx.id AS id_rx, rx.time AS time_rx, rx.band AS band_rx, rx.frequency AS f_rx_Hz, rx.tx_sign AS Callsign_tx , rx.power AS power_rx,  rx.rx_sign AS Callsign_rx ,  tx.snr AS SNR_rx FROM wspr.rx tx INNER JOIN wspr.rx rx  ON tx.tx_sign = rx.rx_sign  AND tx.rx_sign = rx.tx_sign AND abs(toUnixTimestamp(tx.time) - toUnixTimestamp(rx.time)) <= 300 WHERE  tx.time BETWEEN '2025-07-09' AND '2025-07-09 23:59:59' AND  rx.time BETWEEN '2025-07-09' AND '2025-07-09 23:59:59' AND tx.tx_sign ='EA5JTT' AND tx.rx_sign NOT IN ('G4LEN','DF1VB' ) ORDER BY  tx.time ASC;