0

I found this on StackOverflow at https://stackoverflow.com/questions/11761938/how-to-get-week-start-and-end-date-string-in-postgresql

You can use date_trunc(‘week’, …).

For example:

SELECT date_trunc(‘week’, ‘2012-07-25 22:24:22’::timestamp);
-> 2012-07-23 00:00:00
Then, you can convert this into a date, if you’re not interested in a start time.

To get the end date too:

SELECT date_trunc(‘week’, ‘2012-07-25 22:24:22’::timestamp)::date
|| ‘ ‘
|| (date_trunc(‘week’, ‘2012-07-25 22:24:22’::timestamp)+ ‘6 days’::interval)::date;

-> 2012-07-23 2012-07-29
(I’ve used the default formatting here, you can of course adapt this to use MM/DD/YYYY.)

Note that, if you want to make comparisons on timestamps, instead of using (date_trunc(‘week’, …) + ‘6 days’::interval, you might want to add an entire week and use a strict comparison for the end of the week.

This will exclude y timestamps on the last day of the week (since the cut-off time is midnight on the day).

date_trunc(‘week’, x)::date <= y::timestamp
AND y::timestamp <= (date_trunc(‘week’, x) + ‘6 days’::interval)::date
This will include them:

date_trunc(‘week’, x)::date <= y::timestamp
AND y::timestamp < (date_trunc(‘week’, x) + ‘1 week’::interval)
(That’s in the rare cases when you can’t use date_trunc on y directly.)

If your week starts on a Sunday, replacing date_trunc(‘week’, x)::date with date_trunc(‘week’, x + ‘1 day’::interval)::date – ‘1 day’::interval should work.

Changed status to publish