CODEWITHSUNDEEP
postgresql
sharayu salunkhe
sharayu salunkhe

Reputation: 119

How to find Last Week entries and This Week entries from postgres tables

I want to find the LastWeek entries from postgres table with cycle from Monday to Sunday (both inclusive) For eg - if I query the data today i.e on 2020/07/26 (or say if i query data on any date between 2020/07/20 to 2020/07/26) i should get the data from 2020/07/13 to 2020/07/19 Query:

Select user, date_sent 
from users 
where date_sent between (SELECT current_date - cast(extract(dow from current_date) as int) - 6) 
                    and (SELECT current_date - cast(extract(dow from current_date) as int) + 1)

Similarly I want to find the This Week entries week starting from Monday and ending on present date. For eg - If I query the data today i.e on 2020/07/26 I should get the data from 2020/07/20 to 2020/07/26. If i query on 2020/07/24 then I should get 2020/07/20 to 2020/07/24 Query:

select user, date_sent 
from users 
where date_sent >= date_trunc('week', current_date) 
  and date_sent <= date_trunc('day',current_date+1)

Upvotes: 1

Views: 151

Answers (2)

Belayer
Belayer

Reputation: 14934

Your desired results are inconsistent. In your description, before your initial query you state:

if I query the data today i.e on 2020/07/26 (or say if i query data on any date between 2020/07/20 to 2020/07/26) i should get the data from 2020/07/13 to 2020/07/19

But after that query you state:

If I query the data today i.e on 2020/07/26 I should get the data from 2020/07/20 to 2020/07/26.

You cannot have both.

Assuming the latter to be correct and assuming ISO-8601 week definition, then your request can be re-phased as:

Given a specified date, if that date falls in the same week as the current date then return the dates from the start of the week to the specified date, inclusive. If the specified date does not fall in the current week return the dates return the dates from Monday on or prior to the specified date through Sunday on or after the specified date, inclusive.

The following implements that.

with targets (for_week_containing_date
             ,from_week_start
             ,iso_from_week
             ,iso_this_week) as 
     ( select &for_week_containing_date
            , date_trunc('week', &for_week_containing_date)
            , extract(week from &for_week_containing_date)
            , extract(week from now())
            ) 
select user, date_sent
  from user_days
 cross join targets 
 where 1=1
   and date_sent >= from_week_start
   and date_sent <= case when iso_from_week = iso_this_week
                         then for_week_containing_date
                         else from_week_start + interval '6 days'
                    end 
   ;

Since I do not care much for substitution variables this would need bound variables from a script, or wrap wrap it in an SQL function. See example of that here. Also note the last 2 queries, make sure you are ok with and understand what's happening around year end. You may need to make end of year/ begin of year adjustments. The results are not from being in a function, but result from ISO-8601 definitions. End of year/Begin year checking is needed any time you deal with date ranges.

Upvotes: 1

user330315
user330315

Reputation:

You are almost there.

For "this week":

select user, date_sent 
from users 
where date_sent >= date_trunc('week', current_date) 
  and date_sent < date_trunc('week', current_date) + interval '1 week';

For last week it's quite similar:

select user, date_sent 
from users 
where date_sent >= date_trunc('week', current_date) - interval '1 week'
  and date_sent < date_trunc('week', current_date)

Upvotes: 1

Related Questions