4

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)
planetp
  • 14,248
  • 20
  • 86
  • 160

2 Answers2

4

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

S-Man
  • 22,521
  • 7
  • 40
  • 63
  • One downside of this approach is that it will only work for `date` type, while `date_trunc` works with both `date` and `timestamp`. – Eugene Yarmash Jun 21 '19 at 11:43
  • Yes you are right. But you could cast the timestamp into date before the operation nonetheless because the time part will be truncated anyway. – S-Man Jun 21 '19 at 11:46
3

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).

Eugene Yarmash
  • 142,882
  • 41
  • 325
  • 378