-2

I am using Flask SQLAlchemy and Postgres, I was wondering how can I delete data from a table if it goes back, for example 7 days from the CURRENT date.

My table has 3 rows, name, hobby and a date_posted, all of them are STRING.

The datetime format I am using is

"%Y-%m-%d %H:%M:%S"
Robinson Dickinson
  • 1
  • 1
  • 2

1 Answers1

0

This only works if you store your datetime in the ISO 8601 format. There may be other formats that allow for the same lexicographical ordering; I'm just not aware of them so I'm being safe.

Really, it would be easier if you stored in DateTime format, but anyway.

import datetime as dt

DAYS = 30

cutoff = (dt.date.today() - dt.timedelta(days=DAYS)).strftime('%Y-%m-%d')
User.query.filter_by(date_added<=cutoff).delete()
db.session.commit()

If you do start storing timestamps as DateTime but want to filter by date, you can use cast and Date

roganjosh
  • 12,594
  • 4
  • 29
  • 46
  • Got it, thank you so much. I will reformat my table so it takes in a DateTime instead of a string! – Robinson Dickinson Sep 01 '20 at 19:40
  • It'll be much easier. I can add a little extra how to deal with `DateTime` vs `Date` types if that's helpful, @RobinsonDickinson – roganjosh Sep 01 '20 at 19:41
  • Sure, that would be helpful. – Robinson Dickinson Sep 01 '20 at 19:43
  • @RobinsonDickinson you should use `DateTime(timezone=True)` whenever you can. timestamps without time-zones are mainly there to support older DBs. If you create a new DB you should add time-zones. It is a are critical piece of information for time-related data. Even if you are using UTC because otherwise you will never know if you need to apply DST or not. – exhuma Sep 01 '20 at 19:54
  • @exhuma totally valid point. I'm half tempted to add it to my answer but I'd rather give you the chance to add another answer. I (think) I fixed the comparison in the question itself, but happy to upvote that – roganjosh Sep 01 '20 at 20:02