Why were all rows deleted when trying to delete old rows with date query

I have a table with column name date of type timestamp

I’m trying to delete old rows with the following query:

delete from my_table where date<(now()-'30 days'::interval)

but all rows are deleted.

For example suppose I have a table with 2 rows:

date
2020-02-07 13:53:12.000
2020-02-09 09:30:12.000

I expect that the query will delete the first row (now=02 sep 2020 - 30 days = 02 aug 2020) but all rows where deleted

How to fix the query to remove the rows I want?

Answer

The standard formatting is YYYY-MM-DD HH24:MI:SS:MS. Both dates are in February, so they are earlier than August the second. You’ve probably meant to check against 2020-09-02 09:30:12.000 etc. More information you can find in the official documentation.