[sql] How to list records with date from the last 10 days?

SELECT Table.date FROM Table WHERE date > current_date - 10;

Does this work on PostgreSQL?

This question is related to sql postgresql

The answer is


I would check datatypes.

current_date has "date" datatype, 10 is a number, and Table.date - you need to look at your table.


http://www.postgresql.org/docs/current/static/functions-datetime.html shows operators you can use for working with dates and times (and intervals).

So you want

SELECT "date"
FROM "Table"
WHERE "date" > (CURRENT_DATE - INTERVAL '10 days');

The operators/functions above are documented in detail:


Just generalising the query if you want to work with any given date instead of current date:

SELECT Table.date
  FROM Table 
  WHERE Table.date > '2020-01-01'::date - interval '10 day'

My understanding from my testing (and the PostgreSQL dox) is that the quotes need to be done differently from the other answers, and should also include "day" like this:

SELECT Table.date
  FROM Table 
  WHERE date > current_date - interval '10 day';

Demonstrated here (you should be able to run this on any Postgres db):

SELECT DISTINCT current_date, 
                current_date - interval '10' day, 
                current_date - interval '10 days' 
  FROM pg_language;

Result:

2013-03-01  2013-03-01 00:00:00 2013-02-19 00:00:00

you can use between too:

SELECT Table.date
  FROM Table 
  WHERE date between current_date and current_date - interval '10 day';