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

user_275088 0
Viewed 2094

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)
dayofweek weekday truncate date postgresql
2 Answers

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

user_39842210

You could truncate the date to the week's Monday, then subtract 1 day, 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).

user_2442970
Related