Postgresql Query to Select Data from Last Week

PostgreSQL Query to select data from last week?

This condition will return records from Sunday till Saturday last week:

WHERE created BETWEEN
    NOW()::DATE-EXTRACT(DOW FROM NOW())::INTEGER-7 
    AND NOW()::DATE-EXTRACT(DOW from NOW())::INTEGER

There is an example:

WITH compras AS (
    SELECT ( NOW() + (s::TEXT || ' day')::INTERVAL )::TIMESTAMP(0) AS created
    FROM generate_series(-20, 20, 1) AS s
)
SELECT to_char( created, 'DY'::TEXT), created
FROM compras
WHERE created BETWEEN
    NOW()::DATE-EXTRACT(DOW FROM NOW())::INTEGER-7 
    AND NOW()::DATE-EXTRACT(DOW from NOW())::INTEGER

In answer to @d456:

Wouldn't using BETWEEN include midnight on Sunday at both ends of the interval?

That right, BETWEEN includes midnight on Sunday at both ends of the interval. To exclude midnight on Sunday at end of interval it is necessary to use operators >= and <:

WITH compras AS (
    SELECT s as created
    FROM generate_series( -- this would produce timestamps with 20 minutes step
             (now() - '20 days'::interval)::date,
             (now() + '20 days'::interval)::date,
             '20 minutes'::interval) AS s
)
SELECT to_char( created, 'DY'::TEXT), created
FROM compras
WHERE TRUE
    AND created >= NOW()::DATE-EXTRACT(DOW FROM NOW())::INTEGER-7
    AND created <  NOW()::DATE-EXTRACT(DOW from NOW())::INTEGER

get data from last week monday to last week sunday in postgresql

One option uses DATE_TRUNC:

SELECT AVG(voltage)
FROM TABLE1 t1
INNER JOIN TABLE2 t2
    ON t1.id = t2.table1_id
WHERE recharged >= DATE_TRUNC('week', NOW()) - interval '7 day' AND
      recharged < DATE_TRUNC('week', NOW());

Postgres' DATE_TRUNC() function treats Monday as the start of the week.

Select last Week or Weekend from the current_date on postgresql

I solved!

select current_date - extract(isodow from current_date)::integer-6 as monday,
   current_date - extract(isodow from current_date)::integer-2 as friday,
   current_date - extract(isodow from current_date)::integer-1 as saturday,
   current_date - extract(isodow from current_date)::integer as sunday

It worked for all date. I only have to use case when to chose the different 2 select statements.

Thank you all

How do you find results that occurred in the past week?

You want to use interval and current_date:

select * from books where returned_date > current_date - interval '7 days'

This would return data from the past week including today.

Here's more on working with dates in Postgres.

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

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)

How to list last 7 days records in postgresql?

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'.

I want to get last week data and if no sales found for a day then give 0 in postgres

It sounds like you are missing dates from your report when there were no sales on those dates.

Use generate_series() to create the dates of the report, and then left join into your orders:

with report_dates as (
  select gs.ddate::date, min(gs.ddate::date) over () as begin_date
    from generate_series(
           date_trunc('week', now()) - interval '5 days',
           date_trunc('week', now()),
           interval '1 day'
         ) as gs(ddate)
)
select r.ddate, 
       to_char(r.ddate,'Dy'::TEXT) as day,
       coalesce(count(menu_item_id), 0) as qty_sold,
       coalesce(sum(price - cost), 0) as total_profit
  from report_dates r
       left join sales_order s 
         on s.order_time >= r.begin_date
        and s.order_time::ddate = r.ddate
       left join order_item o on o.sales_order_id=s.id
       left join menu_item m on m.id=o.menu_item_id
 group by r.ddate, day
 order by day

PostgreSQL get current week and previous week revenue from Date column

Using your formula as-is, conditional aggregation by week and grouping by "FacilityId":

select 
  "FacilityId",
  sum(... your revenue formula here ...)
   filter (where date_trunc('week', "DeliveryDate") = date_trunc('week', now())) as "CURRENT_WEEK",
  sum(... your revenue formula here ...)
   filter (where date_trunc('week', "DeliveryDate") = date_trunc('week', now() - interval '1 week')) as "LAST_WEEK"
from sale_order so 
inner join order_details od on so.id = od."SaleOrderId"
group by so."FacilityId";

Please note that camel-case names need to be enclosed in double quotes.

Show daily data from current week, Postgresql

The beginning of the current week is date_trunc('week',current_date), so you just want to query dates later later than that:

select
    order_datetime_tz::date AS date,
    orders

FROM
    order_fact f 
    where order_datetime_tz >= date_trunc('week',current_date)

---

Related Topics