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';