1

I'm very new to working with DBs/SQL and doing my best to get adjusted I'm using SQLAlchemy/Postgres to write data from a Python API that I write market data from every 10 minutes (inserted into DB). This scraper is intended to get stock minute "candles", so it should be inserting 10 rows into each stock every time. I started running it last night and noticed the numbers are a bit off, in that I did a SELECT count(*) FROM exchange WHERE market='x_market'; in pgcli and I got a count of 900, where it should be around 1000 (started running 1k minutes ago). Essentially, what I want to do (if possible) is to see if there are any gaps (data it didn't catch) between rows. Each row has a unix timestamp, and each timestamp "below it" should be 60000 ms (1 min) different. I know in Python I could just iterate through and check that, but I am interested in learning more about SQL (would be much nicer to just check on pgcli). Is it possible to check this (with SQL)? I am attaching a screenshot to show schema/what I mean. Thanks very much in advance.

(For pic, timestamp (unix) is first column, and the remaining others are just the equity price data) enter image description here

wildplasser
  • 43,142
  • 8
  • 66
  • 109
Michael W
  • 343
  • 2
  • 11
  • Short answer: yes, it is possible. Please add your tabledefinition(s) and some data to your question **as text**, not as a picture. And show us what you tried. – wildplasser Feb 25 '19 at 20:15
  • @wildplasser Sorry, the table definition is `data(exchange (string), market (string), timestamp (int), open (float), high (float), low (float), close (float), volume (float) )` . As for what I tried, I'm very new to sql/querying in general so I've just tried reordering and looking for gaps but can't find any. The somewhat similar answers I found online are way beyond me/didn't seem to work for me. – Michael W Feb 25 '19 at 20:52
  • 1
    `... to your **question** as text ...` Not as a comment. Plus: it is hard to search for not-existing records. That is your problem, basically. – wildplasser Feb 25 '19 at 21:07
  • 1
    @wildplasser but even if I know which ones should exist? I know that from the beginning till end, it should have timestamps with each one increasing by 60000 (1 min). Knowing this, would it be possible to find two rows where there is a gap? – Michael W Feb 25 '19 at 21:23
  • Yes,look for two adjacent *existing* rows that have a gap larger than 1min.Basically window()+lag/lead – wildplasser Feb 25 '19 at 23:04

1 Answers1

1

One way to get the gaps or "missing" timestamps would be to use generate_series to create a series of timestamps separated by 60000ms, then join to your table. Like so:

-- Simulate some data with a gap
WITH exchange(tstamp, val) AS
(
  VALUES
    (1551118800000, 0.03626),
    (1551118860000, 0.036243),
    (1551118980000, 0.03627)
)

-- Return timestamp(s) that do not match to exchange table data
SELECT stamps.tstamp AS missing_timestamp
FROM exchange
RIGHT JOIN generate_series(1551118800000, 1551118980000, 60000) stamps(tstamp)
  ON exchange.tstamp = stamps.tstamp
WHERE exchange.tstamp IS NULL

-- Or using a more explicit anti-JOIN (cleaner?)
SELECT stamps.tstamp AS missing_timestamp
FROM generate_series(1551118800000, 1551118980000, 60000) stamps(tstamp)
WHERE NOT EXISTS (SELECT 1 FROM exchange WHERE tstamp = stamps.tstamp)

The first argument in generate_series is the minimum timestamp and the second is the maximum. You can alter for your use case. The final argument is the 'step' (60000ms).

Result (given the simulated data above):

missing_timestamp
------
1551118920000
Nick
  • 7,103
  • 2
  • 21
  • 43
  • For large ranges, you generally don't want generate_series() : they'll spoil the planning. – wildplasser Feb 25 '19 at 22:31
  • This is an incredibly great idea. Thank you so much! – Michael W Feb 28 '19 at 00:00
  • I know this is a bit late, but for the anti-join way, could you explain the FROM clause what stamps(tstamp) is versus stamps.tstamp? What does calling one mean versus calling another? When do you use parens versus using the dot operator? Does the generate series get assigned to stamps(tstamp)? Thanks so much – Michael W Mar 26 '19 at 19:29
  • `stamps(tstamp)` is an alias for the generate_series "table". The syntax is `table(column)` so you can access it like a real table. Inside of the `WHERE NOT EXISTS`, the table alias is needed to disambiguate between the `tstamp` field in `exchange` and in `stamps` since they have the same name. – Nick Mar 26 '19 at 20:15