postgresql 在 Postgres 中获取本周星期一的日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27989762/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get this week's monday's date in Postgres?
提问by Dezzie
How can I get this week's monday's date in PostgreSQL?
如何在 PostgreSQL 中获取本周的星期一日期？
For example, today is 01/16/15 (Friday). This week's monday date is 01/12/15.
例如，今天是 01/16/15（星期五）。本周的星期一日期是 15 年 1 月 12 日。
采纳答案by John Powell
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.
一般的想法是获取一周中的当前日期dow，减去 7，然后获取 abs，这将为您提供到一周结束的天数，然后加 1 以到达星期一。这给你下周一。
EDIT: having completely misread the question, to get the prior Monday, is much simpler:
编辑：完全误读了这个问题，以获得前一个星期一，要简单得多：
SELECT current_date - cast(extract(dow from current_date) as int) + 1;
ie, subtract the current day of the week from today's date (the number of day's path Monday) and add one, to get back to Monday.
即，从今天的日期（星期一路径的天数）中减去一周中的当前日期并加一，以返回到星期一。
回答by a_horse_with_no_name
You can use date_trunc()for this:
您可以date_trunc()为此使用：
select date_trunc('week', current_date);
More details in the manual:
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
手册中的更多详细信息：http:
//www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
If "today" is Monday it will return today's date.
如果“今天”是星期一，它将返回今天的日期。
回答by Mike Sherrill 'Cat Recall'
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 obviouslycorrect.
- 简单的。初级开发人员只需很少的培训就可以正确查询它。
- 明显的。正确的查询显然是正确的。
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;
回答by Adam G.
And for other mondays:
其他星期一：
Next Monday:
下个星期一：
date_trunc('week', now())+ INTERVAL '7days'
Last week's monday:
上周的星期一：
date_trunc('week', now())- INTERVAL '7days'
etc. :)
等等。 ：）
