Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@lnostdal

lnostdal/postgresql_bitmex_quote_data_test.clj Secret

Created November 12, 2018 17:48
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save lnostdal/9e7e710290cd7448cd81b29fc744fbc7 to your computer and use it in GitHub Desktop.
Save lnostdal/9e7e710290cd7448cd81b29fc744fbc7 to your computer and use it in GitHub Desktop.
Download ZIP
postgresql_bitmex_quote_data_test.clj
Raw
postgresql_bitmex_quote_data_test.clj
;; Need the open and close of these "candles" still, but OK:
(jdbc/with-db-connection [conn -db-conn-]
(let [t (time.coerce/from-string "2018-02-01")]
(time
(dotimes [i 50]
(let [ts-start (time/plus t (time/hours i))
ts-end (time/plus t (time/hours (inc i)))]
(time (println {:data (jdbc/query conn ["SELECT MAX(bid_price) AS max_bid_price, MIN(bid_price) AS min_bid_price, MAX(ask_price) AS max_ask_price, MIN(ask_price) AS min_ask_price FROM bitmex_quote WHERE timestamp >= ? AND timestamp < ? AND symbol = 'XBTUSD';"
(time.coerce/to-sql-time ts-start) (time.coerce/to-sql-time ts-end)])
:timestamp (str ts-end)})))))))
{:data ({:max_bid_price 10195.0, :min_bid_price 10051.0, :max_ask_price 10199.0, :min_ask_price 10058.5}), :timestamp 2018-02-01T01:00:00.000Z}
"Elapsed time: 37.605161 msecs"
{:data ({:max_bid_price 10166.5, :min_bid_price 10043.0, :max_ask_price 10180.5, :min_ask_price 10044.5}), :timestamp 2018-02-01T02:00:00.000Z}
"Elapsed time: 25.175765 msecs"
{:data ({:max_bid_price 10149.5, :min_bid_price 10053.5, :max_ask_price 10150.0, :min_ask_price 10056.0}), :timestamp 2018-02-01T03:00:00.000Z}
"Elapsed time: 25.250413 msecs"
{:data ({:max_bid_price 10123.5, :min_bid_price 9803.5, :max_ask_price 10126.5, :min_ask_price 9823.0}), :timestamp 2018-02-01T04:00:00.000Z}
"Elapsed time: 24.262265 msecs"
{:data ({:max_bid_price 10106.0, :min_bid_price 9850.0, :max_ask_price 10108.5, :min_ask_price 9850.5}), :timestamp 2018-02-01T05:00:00.000Z}
"Elapsed time: 26.850656 msecs"
{:data ({:max_bid_price 10167.0, :min_bid_price 10047.0, :max_ask_price 10167.5, :min_ask_price 10050.5}), :timestamp 2018-02-01T06:00:00.000Z}
"Elapsed time: 25.334787 msecs"
{:data ({:max_bid_price 10146.5, :min_bid_price 10000.0, :max_ask_price 10147.0, :min_ask_price 10000.5}), :timestamp 2018-02-01T07:00:00.000Z}
"Elapsed time: 24.4304 msecs"
{:data ({:max_bid_price 10090.0, :min_bid_price 9970.5, :max_ask_price 10094.5, :min_ask_price 9971.5}), :timestamp 2018-02-01T08:00:00.000Z}
"Elapsed time: 24.384435 msecs"
{:data ({:max_bid_price 9988.5, :min_bid_price 9656.0, :max_ask_price 9989.0, :min_ask_price 9669.0}), :timestamp 2018-02-01T09:00:00.000Z}
"Elapsed time: 26.136273 msecs"
{:data ({:max_bid_price 9852.0, :min_bid_price 9461.0, :max_ask_price 9862.0, :min_ask_price 9465.0}), :timestamp 2018-02-01T10:00:00.000Z}
"Elapsed time: 23.282149 msecs"
{:data ({:max_bid_price 9588.0, :min_bid_price 9331.0, :max_ask_price 9589.0, :min_ask_price 9338.0}), :timestamp 2018-02-01T11:00:00.000Z}
"Elapsed time: 23.655192 msecs"
{:data ({:max_bid_price 9568.0, :min_bid_price 9369.0, :max_ask_price 9570.0, :min_ask_price 9369.5}), :timestamp 2018-02-01T12:00:00.000Z}
"Elapsed time: 24.219649 msecs"
{:data ({:max_bid_price 9519.5, :min_bid_price 9219.0, :max_ask_price 9523.0, :min_ask_price 9220.5}), :timestamp 2018-02-01T13:00:00.000Z}
"Elapsed time: 24.00672 msecs"
{:data ({:max_bid_price 9368.0, :min_bid_price 9081.0, :max_ask_price 9372.0, :min_ask_price 9081.5}), :timestamp 2018-02-01T14:00:00.000Z}
"Elapsed time: 20.240013 msecs"
{:data ({:max_bid_price 9415.0, :min_bid_price 9168.0, :max_ask_price 9422.0, :min_ask_price 9170.0}), :timestamp 2018-02-01T15:00:00.000Z}
"Elapsed time: 23.753341 msecs"
{:data ({:max_bid_price 9336.5, :min_bid_price 9112.0, :max_ask_price 9340.0, :min_ask_price 9116.0}), :timestamp 2018-02-01T16:00:00.000Z}
"Elapsed time: 25.75577 msecs"
{:data ({:max_bid_price 9227.0, :min_bid_price 8850.0, :max_ask_price 9227.5, :min_ask_price 8868.5}), :timestamp 2018-02-01T17:00:00.000Z}
"Elapsed time: 24.70308 msecs"
{:data ({:max_bid_price 9075.0, :min_bid_price 8901.0, :max_ask_price 9079.0, :min_ask_price 8905.5}), :timestamp 2018-02-01T18:00:00.000Z}
"Elapsed time: 22.363267 msecs"
{:data ({:max_bid_price 9058.5, :min_bid_price 8920.0, :max_ask_price 9060.0, :min_ask_price 8925.5}), :timestamp 2018-02-01T19:00:00.000Z}
"Elapsed time: 26.077434 msecs"
{:data ({:max_bid_price 9003.0, :min_bid_price 8700.0, :max_ask_price 9009.0, :min_ask_price 8702.5}), :timestamp 2018-02-01T20:00:00.000Z}
"Elapsed time: 22.004531 msecs"
{:data ({:max_bid_price 9216.0, :min_bid_price 8475.5, :max_ask_price 9225.5, :min_ask_price 8480.0}), :timestamp 2018-02-01T21:00:00.000Z}
"Elapsed time: 21.434621 msecs"
{:data ({:max_bid_price 9233.5, :min_bid_price 8969.0, :max_ask_price 9243.0, :min_ask_price 8969.5}), :timestamp 2018-02-01T22:00:00.000Z}
"Elapsed time: 22.497618 msecs"
{:data ({:max_bid_price 9122.0, :min_bid_price 8865.0, :max_ask_price 9124.5, :min_ask_price 8869.5}), :timestamp 2018-02-01T23:00:00.000Z}
"Elapsed time: 27.506765 msecs"
{:data ({:max_bid_price 9098.5, :min_bid_price 8850.5, :max_ask_price 9099.0, :min_ask_price 8851.0}), :timestamp 2018-02-02T00:00:00.000Z}
"Elapsed time: 25.865407 msecs"
{:data ({:max_bid_price 9029.0, :min_bid_price 8569.0, :max_ask_price 9035.0, :min_ask_price 8573.0}), :timestamp 2018-02-02T01:00:00.000Z}
"Elapsed time: 24.281225 msecs"
{:data ({:max_bid_price 8740.0, :min_bid_price 8535.5, :max_ask_price 8742.5, :min_ask_price 8537.0}), :timestamp 2018-02-02T02:00:00.000Z}
"Elapsed time: 21.925361 msecs"
{:data ({:max_bid_price 8846.5, :min_bid_price 8555.0, :max_ask_price 8847.0, :min_ask_price 8558.0}), :timestamp 2018-02-02T03:00:00.000Z}
"Elapsed time: 25.60544 msecs"
{:data ({:max_bid_price 8915.0, :min_bid_price 8661.5, :max_ask_price 8916.0, :min_ask_price 8662.5}), :timestamp 2018-02-02T04:00:00.000Z}
"Elapsed time: 23.134446 msecs"
{:data ({:max_bid_price 8733.5, :min_bid_price 8500.0, :max_ask_price 8734.5, :min_ask_price 8510.0}), :timestamp 2018-02-02T05:00:00.000Z}
"Elapsed time: 27.638799 msecs"
{:data ({:max_bid_price 8679.5, :min_bid_price 8450.0, :max_ask_price 8680.0, :min_ask_price 8457.5}), :timestamp 2018-02-02T06:00:00.000Z}
"Elapsed time: 23.982419 msecs"
{:data ({:max_bid_price 8634.5, :min_bid_price 8288.0, :max_ask_price 8637.5, :min_ask_price 8290.5}), :timestamp 2018-02-02T07:00:00.000Z}
"Elapsed time: 22.552115 msecs"
{:data ({:max_bid_price 8682.0, :min_bid_price 8403.0, :max_ask_price 8683.5, :min_ask_price 8408.0}), :timestamp 2018-02-02T08:00:00.000Z}
"Elapsed time: 23.176183 msecs"
{:data ({:max_bid_price 8440.5, :min_bid_price 8240.0, :max_ask_price 8446.0, :min_ask_price 8246.5}), :timestamp 2018-02-02T09:00:00.000Z}
"Elapsed time: 22.568877 msecs"
{:data ({:max_bid_price 8438.5, :min_bid_price 8103.0, :max_ask_price 8440.5, :min_ask_price 8105.0}), :timestamp 2018-02-02T10:00:00.000Z}
"Elapsed time: 20.694611 msecs"
{:data ({:max_bid_price 8321.0, :min_bid_price 8080.0, :max_ask_price 8324.0, :min_ask_price 8085.0}), :timestamp 2018-02-02T11:00:00.000Z}
"Elapsed time: 23.822073 msecs"
{:data ({:max_bid_price 8204.5, :min_bid_price 7929.0, :max_ask_price 8205.0, :min_ask_price 7930.0}), :timestamp 2018-02-02T12:00:00.000Z}
"Elapsed time: 20.863328 msecs"
{:data ({:max_bid_price 8165.0, :min_bid_price 7610.0, :max_ask_price 8199.0, :min_ask_price 7614.0}), :timestamp 2018-02-02T13:00:00.000Z}
"Elapsed time: 22.818748 msecs"
{:data ({:max_bid_price 8764.0, :min_bid_price 8140.0, :max_ask_price 8776.5, :min_ask_price 8142.5}), :timestamp 2018-02-02T14:00:00.000Z}
"Elapsed time: 21.00293 msecs"
{:data ({:max_bid_price 8831.0, :min_bid_price 8416.0, :max_ask_price 8870.0, :min_ask_price 8433.5}), :timestamp 2018-02-02T15:00:00.000Z}
"Elapsed time: 25.368282 msecs"
{:data ({:max_bid_price 8988.5, :min_bid_price 8550.0, :max_ask_price 8991.0, :min_ask_price 8551.0}), :timestamp 2018-02-02T16:00:00.000Z}
"Elapsed time: 24.305846 msecs"
{:data ({:max_bid_price 9052.0, :min_bid_price 8822.0, :max_ask_price 9054.5, :min_ask_price 8825.5}), :timestamp 2018-02-02T17:00:00.000Z}
"Elapsed time: 27.122503 msecs"
{:data ({:max_bid_price 9042.0, :min_bid_price 8600.5, :max_ask_price 9042.5, :min_ask_price 8601.0}), :timestamp 2018-02-02T18:00:00.000Z}
"Elapsed time: 25.678214 msecs"
{:data ({:max_bid_price 8784.0, :min_bid_price 8447.0, :max_ask_price 8789.0, :min_ask_price 8474.0}), :timestamp 2018-02-02T19:00:00.000Z}
"Elapsed time: 27.757829 msecs"
{:data ({:max_bid_price 8701.0, :min_bid_price 8436.0, :max_ask_price 8708.0, :min_ask_price 8445.0}), :timestamp 2018-02-02T20:00:00.000Z}
"Elapsed time: 24.063865 msecs"
{:data ({:max_bid_price 8752.5, :min_bid_price 8475.0, :max_ask_price 8754.0, :min_ask_price 8484.5}), :timestamp 2018-02-02T21:00:00.000Z}
"Elapsed time: 27.764366 msecs"
{:data ({:max_bid_price 8654.5, :min_bid_price 8451.0, :max_ask_price 8655.0, :min_ask_price 8453.5}), :timestamp 2018-02-02T22:00:00.000Z}
"Elapsed time: 25.48298 msecs"
{:data ({:max_bid_price 8814.0, :min_bid_price 8470.0, :max_ask_price 8820.0, :min_ask_price 8475.0}), :timestamp 2018-02-02T23:00:00.000Z}
"Elapsed time: 26.029201 msecs"
{:data ({:max_bid_price 8874.0, :min_bid_price 8715.0, :max_ask_price 8877.5, :min_ask_price 8715.5}), :timestamp 2018-02-03T00:00:00.000Z}
"Elapsed time: 26.145935 msecs"
{:data ({:max_bid_price 8850.5, :min_bid_price 8655.0, :max_ask_price 8857.0, :min_ask_price 8658.0}), :timestamp 2018-02-03T01:00:00.000Z}
"Elapsed time: 26.728649 msecs"
{:data ({:max_bid_price 8743.0, :min_bid_price 8468.0, :max_ask_price 8743.5, :min_ask_price 8471.0}), :timestamp 2018-02-03T02:00:00.000Z}
"Elapsed time: 28.36013 msecs"
"Elapsed time: 1266.177364 msecs"
quantataraxia.core>
@lnostdal
Copy link
Author 

;; to include Open and Close you could do something like this:

(jdbc/with-db-connection [conn -db-conn-]
  (let [t (time.coerce/from-string "2018-02-01")]
    (time
     (dotimes [i 50]
       (let [ts-start (time/plus t (time/hours i))
             ts-end (time/plus t (time/hours (inc i)))]
         (time (println {:data (jdbc/query conn ["SELECT
        first_value(bid_price) OVER w AS first_bid_price,
        first_value(ask_price) OVER w AS first_ask_price,
        MAX(bid_price) OVER w AS max_bid_price,
        MAX(ask_price) OVER w AS max_ask_price,
        MIN(bid_price) OVER w AS min_bid_price,
        MIN(ask_price) OVER w AS min_ask_price,
        last_value(bid_price) OVER w AS last_bid_price,
        last_value(ask_price) OVER w AS last_ask_price
  FROM bitmex_quote
  WHERE timestamp >= ? AND timestamp < ? AND symbol = 'XBTUSD'
  WINDOW w AS (ORDER BY timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ORDER BY timestamp ASC
  LIMIT 1;"
                                                 (time.coerce/to-sql-time ts-start) (time.coerce/to-sql-time ts-end)])
                         :timestamp (str ts-end)})))))))

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment