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?

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'

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'

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

Related

Recent Posts