Menu
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.
695
In PostgreSQL you can use the extract() function to get the day from a date. You can also use date_part() to do the same thing. When extracting the day from a date, you need to specify what sense of the word “day” you mean. For example, “day of week”, “day of month”, “day of year”, etc.
Use the DATE_PART() function to retrieve the week number from a date in a PostgreSQL database. This function takes two arguments. The first argument is the date part to retrieve; we use 'week', which returns the week number (e.g. “1” for the first week in January, the first week of the year).
What is PostgreSQL Now Function? The Now() function is used to return the current date and time of the time zone (default or user-defined). Its return type is the timestamp with the time zone.
Use current_date to get today's date. Note that you don't need brackets at the end of the current_date function. In PostgreSQL, you can subtract or add any number of days using the INTEGER keyword. Here, since you need to subtract one day, you use - INTEGER '1' to get yesterday's date.
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.
121
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.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
