Home > database >  Fetch data from the past 5 days PostgreSQL
Fetch data from the past 5 days PostgreSQL

Time:05-13

I am trying to return data only from the last five days in PostgreSQL using the following query

SELECT id, id, text, author, updated_at 
FROM public.table 
WHERE date_trunc('day', updated_at) <= CURRENT_DATE - INTERVAL '5 days';

Example data in updated_at field: 2022-03-10 16:46:17.42916

However, the above query returns all the results in the table instead of the required ones. I'm not sure what I am doing wrong here (I'm quite new to PSQL)

The schema of my table is as follows

"id"            "integer"
"updated_at"    "timestamp without time zone"
"tweet_id"      "character varying"
"text"          "character varying"
"author"        "character varying"
"created_at"    "character varying"

CodePudding user response:

If you want to return data from the last 5 days then your inequality needs to be flipped around:

SELECT id, id, text, author, updated_at
FROM public.table
WHERE updated_at >= CURRENT_DATE - INTERVAL '5 days';

CodePudding user response:

Can try this one:

SELECT
    id,
    id,
    text,
    author,
    updated_at 
FROM
    PUBLIC.table 
WHERE
audit_date::DATE BETWEEN NOW()::DATE-'5 DAYS'::INTERVAL AND NOW()::DATE
  • Related