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;

CodePudding user response：

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)

CodePudding user response：

try this : select now() - cast(extract('DoW' from now()) || ' day' as interval)