look for missing datetime records in database I have a python script that scrapes a webpage every minute of every day and saves the results in a postgres database.

As I browse through the database, I've noticed there are minutes here and there that are missing from the database. This may be because of my server's downtime or because of the website downtime (but the reason for the gaps is not relevant to my question here).  

I'm trying to figure out the best way to catalog what minutes are missing from the database.

Right now I have a python script that creates a list of every minute of every day and use that list to iterate through the datetime column in my database table, something like this:

def pd(start, end, delta):
    c = start
    datelist = []
    while c &lt; end:
        datelist.append(c)
        c += delta
    return datelist


Is there a way to do this directly using SQL?
You can do something similar.  First generate the minutes (if you do this often, probably you would like to store them in a permanent, indexed table), then pick the ones not in your table.  The query would look like (no looping!):

WITH minutes(m) AS (
    SELECT i::timestamp(0) 
    FROM generate_series('2013-04-17 12:00:00', '2013-04-17 13:00:00', interval '1 minute') t(i)
)
SELECT m
FROM 
    minutes
    LEFT JOIN your_table ON m = your_timestamp
WHERE your_table_id IS NULL;


You may have to transform your timestamp stored if the seconds part is non-zero.  And, with big amounts of data at least, you will need an index defined on the timestamp (and possibly your_table_id, it would help a lot if you use PostgreSQL 9.2).