Skip to content

SQL - PostgreSQL - Filter for the Last x Days

Source: sql-snippets/last-x-days.md at main · count/sql-snippets (github.com)

Description

Often you only want to return the last X days of a query (e.g. last 30 days) and you want that to always be up to date (dynamic).

This snippet can be added to your WHERE clause to filter for the last X days:

SELECT
   <COL1>,...<COLN>,
   <DATE_COLUMN>
FROM
   <TABLE>
WHERE
   <DATE_COLUMN> > current_date - interval 'X' day

where:

  • <COL1>,...<COLN> are all the columns you want to return in your query
  • <DATE_COLUMN> is your date column you want to filter on
  • X is the number of days you want to filter on

For reference see Postgres’s date/time Functions and Operators.

select 
  day,
  rank,
  title,
  artist,
  streams
from 
   "public".spotify_daily
where 
   day > current_date - interval '180' day
   and artist = 'Drake'

Output:

day rank title artist streams
06/04/2021 46 What’s Next Drake 1,524,267
… … … … …

Backlinks:

list from [[SQL - PostgreSQL - Filter for the Last x Days]] AND -"Changelog"