36

How can I get this week's monday's date in PostgreSQL?

For example, today is 01/16/15 (Friday). This week's monday date is 01/12/15.

Dezzie
  • 934
  • 3
  • 18
  • 35

4 Answers4

63

You can use date_trunc() for this:

select date_trunc('week', current_date);

More details in the manual:
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

If "today" is Monday it will return today's date.

  • 4
    Doesn't the output of this depend on whatever the week-start day (sunday or monday, typically) is set somewhere? – pmarreck Mar 24 '19 at 20:34
37
SELECT current_date + cast(abs(extract(dow FROM current_date) - 7) + 1 AS int);

works, although there might be more elegant ways of doing it.

The general idea is to get the current day of the week, dow, subtract 7, and take the abs, which will give you the number of days till the end of the week, and add 1, to get to Monday. This gives you next Monday.

EDIT: having completely misread the question, to get the prior Monday, is much simpler:

SELECT current_date - ((6 + cast(extract(dow FROM current_date) AS int)) % 7)

ie, subtract the current day of the week from today's date (the number of day's past Monday) and add one, to get back to Monday.

John Powell
  • 12,253
  • 6
  • 59
  • 67
  • That gives me the upcoming Monday Date. How can I get the last monday date? – Dezzie Jan 16 '15 at 18:16
  • 6
    The edited asnswer is slightly wrong for sundays, as any sunday is set to the next day. To convert sundays 6 days back use `current_date - ((6 + cast(extract(dow from current_date) as int)) % 7)` – H4kor Apr 10 '19 at 12:58
6

And for other mondays:

Next Monday: 

date_trunc('week', now())+ INTERVAL '7days'

Last week's monday:

date_trunc('week', now())- INTERVAL '7days'

etc. :)

Adam G.
  • 147
  • 2
  • 2
4

I usually use a calendar table. There are two main advantages.

  • Simple. Junior devs can query it correctly with little training.
  • Obvious. Correct queries are obviously correct.

Assuming that "this week's Monday" means the Monday before today, unless today is Monday then . . .

select max(cal_date) as previous_monday
from calendar
where day_of_week = 'Mon' 
  and cal_date <= current_date;
Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185