CODEWITHSUNDEEP
sqlpostgresql
pythoncoder
pythoncoder

Reputation: 675

How to list last 7 days records in postgresql?

SELECT * FROM dummy WHERE entry_date between current_date and current_date at time zone 'UTC' - interval '7 days';

I want to display current date and previous 7 days. the query doesnt work for me.

SELECT * FROM dummy
 WHERE entry_date  between current_date  and current_date at time zone 'UTC' - interval '7 days

Upvotes: 5

Views: 8158

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I doubt you have future entry dates. So don't use between. Instead:

where entry_date >= current_date at time zone 'UTC' - interval '7 days'

Note: If you want to count the current date as a day, then you want interval '6 days'.

Upvotes: 9

Ehtsham Zafar
Ehtsham Zafar

Reputation: 11

WITH CTE_Weekday(DayNumber, [DayName], DayDate ) AS (SELECT 7, DATENAME(weekday, GETDATE()) AS [DayName], CAST(GETDATE() AS DATE) AS [DayDate]
UNION ALL SELECT DayNumber - 1, DATENAME(weekday, DATEADD(day, DayNumber, GETDATE())) AS [DayName], DATEADD(Day, DayNumber, CAST(GETDATE() AS DATE)) AS [DayDate]
FROM CTE_Weekday WHERE DayNumber > 1) SELECT DayNumber, [DayName], DayDate
FROM CTE_Weekday

Upvotes: -2

Bohemian
Bohemian

Reputation: 424983

between must specify low to high, but you have done the reverse (current date is greater than the date 7 days ago).

Reverse the parameters given to between:

where entry_date between current_date at time zone 'UTC' - interval '7 days and current_timestamp

Also note changed the "high" range to end with current_timestamp not current_date, because current_date is the previous midnight, but you want everything up to "now".

Upvotes: 1

Related Questions