CODEWITHSUNDEEP
mysqlsql
R_life_R
R_life_R

Reputation: 816

Find closest time in inner join Mysql

I have 2 tables, one with crypto values in USD pulled at different interval (every 30 mn approx).

'crypto' table

   +-----+---------------------+-------------+-------------+-----------+
    | id  | date_hour           | crypto_code | crypto_name | price_usd |
    +-----+---------------------+-------------+-------------+-----------+
    |  13 | 2018-05-24 16:43:29 | BTC         | Bitcoin     | 7553.75   |
    |  14 | 2018-05-24 16:43:29 | ETH         | Ethereum    | 584.764   |
    |  15 | 2018-05-24 16:43:29 | BTM         | Bytom       | 0.617043  |
    |  16 | 2018-05-24 16:43:29 | DCR         | Decred      | 102.578   |
    |  17 | 2018-05-24 16:43:29 | SC          | Siacoin     | 0.0164824 |
    |  18 | 2018-05-24 16:43:29 | ZEC         | Zcash       | 289.619   |
    |  19 | 2018-05-24 17:00:01 | BTC         | Bitcoin     | 7528.51   |
    |  20 | 2018-05-24 17:00:01 | ETH         | Ethereum    | 584.769   |
    |  21 | 2018-05-24 17:00:01 | BTM         | Bytom       | 0.614629  |
    |  22 | 2018-05-24 17:00:01 | DCR         | Decred      | 102.229   |
    |  23 | 2018-05-24 17:00:01 | SC          | Siacoin     | 0.016547  |
    |  24 | 2018-05-24 17:00:01 | ZEC         | Zcash       | 286.252   |
    |  25 | 2018-05-24 17:31:01 | BTC         | Bitcoin     | 7508.15   |
    |  26 | 2018-05-24 17:31:01 | ETH         | Ethereum    | 584.024   |
    |  27 | 2018-05-24 17:31:01 | BTM         | Bytom       | 0.607491  |
    |  28 | 2018-05-24 17:31:01 | DCR         | Decred      | 101.801   |
    |  29 | 2018-05-24 17:31:01 | SC          | Siacoin     | 0.0163432 |
    |  30 | 2018-05-24 17:31:01 | ZEC         | Zcash       | 289.187   |
    |  31 | 2018-05-24 18:00:01 | BTC         | Bitcoin     | 7545.02   |
    |  32 | 2018-05-24 18:00:01 | ETH         | Ethereum    | 587.121   |
    |  33 | 2018-05-24 18:00:01 | BTM         | Bytom       | 0.612734  |
    |  34 | 2018-05-24 18:00:01 | DCR         | Decred      | 102.667   |
    |  35 | 2018-05-24 18:00:01 | SC          | Siacoin     | 0.0165401 |
    |  36 | 2018-05-24 18:00:01 | ZEC         | Zcash       | 289.686   |
    |  37 | 2018-05-24 18:31:01 | BTC         | Bitcoin     | 7574.54   |
    |  38 | 2018-05-24 18:31:01 | ETH         | Ethereum    | 590.538   |
    |  39 | 2018-05-24 18:31:01 | BTM         | Bytom       | 0.620313  |
    |  40 | 2018-05-24 18:31:01 | DCR         | Decred      | 103.306   |
    |  41 | 2018-05-24 18:31:01 | SC          | Siacoin     | 0.0164874 |
    |  42 | 2018-05-24 18:31:01 | ZEC         | Zcash       | 291.421   |
    |  43 | 2018-05-24 19:00:02 | BTC         | Bitcoin     | 7600.11   |
    |  44 | 2018-05-24 19:00:02 | ETH         | Ethereum    | 594.962   |
    |  45 | 2018-05-24 19:00:02 | BTM         | Bytom       | 0.622085  |
    |  46 | 2018-05-24 19:00:02 | DCR         | Decred      | 103.229   |
    |  47 | 2018-05-24 19:00:02 | SC          | Siacoin     | 0.0166324 |
    |  48 | 2018-05-24 19:00:02 | ZEC         | Zcash       | 296.651   |
    |  49 | 2018-05-24 19:31:01 | BTC         | Bitcoin     | 7599.6    |
    |  50 | 2018-05-24 19:31:01 | ETH         | Ethereum    | 593.13    |
    |  51 | 2018-05-24 19:31:01 | BTM         | Bytom       | 0.620733  |
    |  52 | 2018-05-24 19:31:01 | DCR         | Decred      | 101.368   |
    |  53 | 2018-05-24 19:31:01 | SC          | Siacoin     | 0.0165951 |
    |  54 | 2018-05-24 19:31:01 | ZEC         | Zcash       | 292.538   |
    |  55 | 2018-05-24 20:00:01 | BTC         | Bitcoin     | 7603.72   |

And another one which monitors a few wallets every X minutes called 'production'

+-----+----------+---------------------+--------------+
| id  | currency | date_hour           | bal_conf     |
+-----+----------+---------------------+--------------+
|   1 | CMM      | 2018-05-25 08:00:00 | 24.87        |
| 197 | ETH      | 2018-05-24 10:56:53 | 0.82431267   |
| 198 | SIA      | 2018-05-24 10:56:54 | 0.77711006   |
| 199 | BTM      | 2018-05-24 10:58:02 | 81.33782690  |
| 201 | SIA      | 2018-05-24 11:00:23 | 0.77711006   |
| 202 | ETH      | 2018-05-24 11:30:15 | 0.83045272   |
| 203 | SIA      | 2018-05-24 11:30:16 | 0.77711006   |
| 204 | ETH      | 2018-05-24 12:00:23 | 0.83441691   |
| 205 | SIA      | 2018-05-24 12:00:23 | 0.77711006   |
| 206 | ETH      | 2018-05-24 12:30:18 | 0.84046355   |
| 207 | SIA      | 2018-05-24 12:30:31 | 0.77711006   |
| 208 | ETH      | 2018-05-24 13:00:14 | 0.84323421   |
| 209 | SIA      | 2018-05-24 13:00:18 | 0.77711006   |
| 211 | SIA      | 2018-05-24 13:30:18 | 0.77711006   |
| 212 | BTM      | 2018-05-24 13:58:05 | 85.18699829  |
| 213 | BTM      | 2018-05-24 14:00:01 | 85.18699829  |
| 215 | SIA      | 2018-05-24 14:00:19 | 0.77711006   |
| 216 | BTM      | 2018-05-24 14:02:02 | 85.18699829  |
| 217 | ETH      | 2018-05-24 14:02:45 | 0.85096095   |
| 218 | SIA      | 2018-05-24 14:02:54 | 0.77711006   |
| 219 | ETH      | 2018-05-24 14:03:32 | 0.85096095   |
| 220 | SIA      | 2018-05-24 14:03:36 | 0.77711006   |
| 221 | BTM      | 2018-05-24 14:04:01 | 85.18699829  |
| 222 | BTM      | 2018-05-24 14:06:01 | 85.41043305  |
| 223 | ETH      | 2018-05-24 14:09:55 | 0.85096095   |
| 224 | SIA      | 2018-05-24 14:10:17 | 0.77711006   |
| 225 | BTM      | 2018-05-24 14:30:01 | 85.75066324  |
| 226 | ETH      | 2018-05-24 14:30:10 | 0.85363614   |
| 227 | SIA      | 2018-05-24 14:30:13 | 0.77711006   |
| 228 | BTM      | 2018-05-24 15:00:01 | 86.67740965  |
| 229 | ETH      | 2018-05-24 15:00:12 | 0.85694953   |
| 230 | SIA      | 2018-05-24 15:00:14 | 0.77711006   |
| 231 | BTM      | 2018-05-24 15:30:01 | 87.02525693  |
| 233 | SIA      | 2018-05-24 15:30:18 | 0.77711006   |
| 234 | ETH      | 2018-05-24 15:34:33 | 0.86264950   |
| 235 | SIA      | 2018-05-24 15:34:40 | 0.77711006   |
| 236 | ETH      | 2018-05-24 15:38:30 | 0.86264950   |
| 237 | SIA      | 2018-05-24 15:38:31 | 0.77711006   |
| 239 | SIA      | 2018-05-24 15:40:15 | 0.77711006   |
| 240 | BTM      | 2018-05-24 15:44:01 | 87.49497772  |
| 241 | BTC      | 2018-05-24 15:48:37 | 0.03155398   |
| 243 | SIA      | 2018-05-24 15:50:20 | 0.77711006   |
| 244 | BTC      | 2018-05-24 15:51:22 | 0.03155398   |
| 245 | BTM      | 2018-05-24 15:55:01 | 87.67270990  |
| 246 | BTC      | 2018-05-24 15:57:01 | 0.03155398   |
| 247 | BTC      | 2018-05-24 16:00:02 | 0.03155398   |
| 248 | BTM      | 2018-05-24 16:00:01 | 87.67270990  |
| 250 | SIA      | 2018-05-24 16:00:21 | 0.77711006   |
| 251 | ETH      | 2018-05-24 16:10:15 | 0.86810400   |
| 252 | SIA      | 2018-05-24 16:10:19 | 0.77711006   |
| 253 | BTM      | 2018-05-24 16:11:01 | 88.15004778  |
| 254 | BTC      | 2018-05-24 16:19:01 | 0.03155398   |
| 256 | SIA      | 2018-05-24 16:20:19 | 0.77711006   |
| 257 | BTM      | 2018-05-24 16:22:01 | 88.15004778  |

I need to find for every date_hour in the production table the closest value of price_usd (closest in term of timing) to the corresponding crypto_code (production.currency=crypto.currency)

I tried this code:

SELECT t.date_hour,t.currency,t.bal_conf, s.price_usd
FROM production t
INNER JOIN crypto s
 ON(t.date_hour like s.date_hour and s.crypto_code=t.currency)

But it does not return all the production records, I therefore need that in case there is not an exact matching date in the crypto table, to find the closest one.

Upvotes: 1

Views: 46

Answers (1)

sticky bit
sticky bit

Reputation: 37472

I would do that with a subquery, that get the price_usd from crypto where the currency codes match and the absolute value of the difference of the time stamps equals the minimum of the difference of the time stamps for the currency code. We get that minimum in another subquery.

SELECT p.date_hour,
       p.currency,
       p.bal_conf,
       (SELECT c.price_usd
               FROM crypto c
               WHERE c.crypto_code = p.currency
                     AND (SELECT min(abs(timestampdiff(second, ci.date_hour, p.date_hour)))
                                 FROM crypto ci
                                 WHERE ci.crypto_code = p.currency)
                         = abs(timestampdiff(second, c.date_hour, p.date_hour))
               ORDER BY c.date_hour DESC
               LIMIT 1) price_usd
       FROM production p;

The ORDER BY c.date_hour DESC LIMIT 1 just handles the rare event when two or more time stamps from crypto produce the same difference for the given time stamp from production. In such a case the most recent time stamp is used.

Indexes to support this query could be

CREATE INDEX crypto_crypto_code_date_hour
             ON crypto
                (crypto_code,
                 date_hour);

for the innermost subquery and

CREATE INDEX crypto_crypto_code_date_hour_price_usd
             ON crypto
                (crypto_code,
                 date_hour,
                 price_usd);

for the outermost subquery. (And none for the outer query, as there are no conditions for production.)

Upvotes: 1

Related Questions