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