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

SELECT Table.date FROM Table WHERE date > current_dat开发者_如何学运维e - 10;

Does this work on PostgreSQL?

Yes this does work in PostgreSQL (assuming the column "date" is of datatype date) Why don't you just try it?

The standard ANSI SQL format would be:

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

I prefer that format as it makes things easier to read (but it is the same as current_date - 10).

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:

• CURRENT_DATE
• INTERVAL '10 days'

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

The suggested answers already seem to solve the questions. But as an addition I am suggesting to use the NOW() function of PostgreSQL.

SELECT Table.date 
FROM Table 
WHERE date > now() - interval '10' day;

Additionally you can even specifiy the time zone which can be really handy.

NOW () AT TIME ZONE 'Europe/Paris'

Starting with Postgres 9.4 you can use the AGE function:

SELECT Table.date FROM Table WHERE AGE(Table.date) <= INTERVAL '10 day';

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'

I would check datatypes.

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

you can use between too:

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