skip to Main Content

I have a data source with a bunch of time stamps. I want to find the week start (sunday) date from these time stamps.

Eg, I want to go from "2022-11-23 00:00:00.0" –> "2022-11-20" for every date in the column called "Received Date"

SELECT
    CASE
        WHEN sorttype = 'sortable' THEN 'Sort'
        WHEN sorttype = 'SORTABLE' THEN 'Sort'
        ELSE 'Non Sort'
    END AS "Sort Type",
    DATEADD(week, DATEDIFF(week, -1, (order_day + podays_received)), -1) AS "Week Start",
    (order_day + podays_received) AS "Received Date",
    pg_rollup AS "Product Family",
    gl,
    quantity_unpacked AS "Quantity Received"
FROM 
    sba.sba_req_po_received
WHERE
    source = 'OPPORTUNITYBUYING'
    AND country_code = 'US'
    AND NOT buy_type_name = 'Offcycle'
LIMIT 100;

2

Answers


  1. The DATE_TRUNC function – Amazon Redshift can return the start of a week. However, it treats Monday as the start of the week.

    If you want to get Sunday as the start of the week, add a day, then truncate, then subtract a day:

    select (date_trunc('week', timestamp_field + interval '1 day') - interval '1 day')::date
    

    Confused? Here’s an example:

    • 2022-12-23 is a Tuesday
    • Add one day: 2022-11-24 (Wednesday)
    • Truncate the ‘week’: 2022-11-21 (Monday)
    • Subtract a day: 2022-11-20 (Sunday)

    Now let’s try it with a Sunday:

    • 2022-12-04 is a Sunday
    • Add one day: 2022-12-05 (Monday)
    • Truncate the ‘week’: 2022-12-05 (Same Monday)
    • Subtract a day: 2022-12-04 (Sunday)
    Login or Signup to reply.
  2. try this : select now() - cast(extract('DoW' from now()) || ' day' as interval)

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search