Search code examples
pythonsqldatabasesqlitesql-delete

Delete datetime from SQL database based on hour

I'm a python dev, I'm handling an SQL database through sqlite3 and I need to perform a certain SQL query to delete data. I have tables which contain datetime objects as keys. I want to keep only one row per hour (the last record for that specific time) and delete the rest. I also need this to only happen on data older than 1 week.

Here's my attempt:

import sqlite3
c= db.cursor()
c.execute('''DELETE FROM TICKER_AAPL WHERE time < 2022-07-11 AND time NOT IN
    ( SELECT * FROM 
    (SELECT min(time) FROM TICKER_AAPL GROUP BY hour(time)) AS temp_tab);''')

Here's a screenshot of the table itself:

Table example

Solution

  • First change the format of your dates from yyyyMMdd ... to yyyy-MM-dd ..., because this is the only valid text date format for SQLite.

    Then use the function strftime() in your query to get the hour of each value in the column time:

    DELETE FROM TICKER_AAPL 
WHERE time < date(CURRENT_DATE, '-7 day') 
  AND time NOT IN (SELECT MAX(time) FROM TICKER_AAPL GROUP BY strftime('%Y-%m-%d %H', time));