How to truncate a date to the beginning of week (Sunday)?

Question

I need to truncate dates to the start of week, which is Sunday in my case. How can I do this in PostgreSQL? This truncates to Monday:

date_trunc('week', mydate)

Answer 1

If you subtract the dow value (0 for Sundays, 6 for Saturdays) from the current date than you get the previous Sunday which is the begin of your Sunday-based week

demo:db<>fiddle

SELECT 
    my_date - date_part('dow', my_date)::int
FROM
    my_table

Further reading, documentation

Answer 2

You could truncate the date to the week's Monday, then subtract 1 day. To handle Sunday correctly, just add 1 extra day to the initial date, e.g.:

SELECT (date_trunc('week', now() + interval '1 day') - interval '1 day')::DATE;
    date
------------
 2019-06-16

As per documentation, date_trunc() accepts values of type date and timestamp and returns a timestamp (thus the cast at the end).