postgresql 当月的当前周数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18399639/
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
Current week number for current month
提问by overflow
I used following query to get current week
我使用以下查询来获取当前周
select extract(week from current_timestamp)and it is showing 34thats fine.
select extract(week from current_timestamp)它显示那34很好。
But how do i get the current week number of the current month.
但是我如何获得当月的当前周数。
回答by Andomar
You can find the first day of this week with:
您可以通过以下方式找到本周的第一天：
trunc('week', current_date)
And the first day of the first week of this month with:
本月第一周的第一天：
trunc('week', date_trunc('month', current_date))
Subtract the two to find the in-month weeknumber:
将两者相减以找到当月的周数：
extract('day' from date_trunc('week', current_date) -
date_trunc('week', date_trunc('month', current_date))) / 7 + 1
回答by Aley
Try the following
尝试以下
SELECT to_char('2016-05-01'::timestamp, 'W');
回答by Shashank
You can find week number by using day as well like:
您可以使用 day 来查找周数，例如：
select ((date_part('day', current_date)::integer - 1) / 7) +1;
回答by zxc
Im not sure on how does this work on postgreSql (http://www.w3cyberlearnings.com/PostgreSQL_DATE_PART)
but in sql server the example is something like this...
我不确定这在 postgreSql ( http://www.w3cyberlearnings.com/PostgreSQL_DATE_PART)上是如何工作的，
但在 sql server 中，这个例子是这样的......
>SELECT date_part('week', date)
回答by Prahlad Rao
This will give accurate results
这将给出准确的结果
CREATE OR REPLACE FUNCTION week_no(date)
RETURNS integer AS
$BODY$
SELECT
CASE WHEN EXTRACT(DAY FROM $1::TIMESTAMP)::INTEGER = 1
THEN 1
ELSE extract(week from $1:: TIMESTAMP)::integer
- extract(week from ( date_trunc('month', $1::TIMESTAMP) + interval '1 day' ) )::integer + 1
END
$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 100;
CREATE OR REPLACE FUNCTION week_no(date)
RETURNS integer AS
$BODY$
SELECT
CASE WHEN EXTRACT(DAY FROM $1::TIMESTAMP)::INTEGER = 1
THEN 1
ELSE extract(week from $1:: TIMESTAMP)::integer
- extract(week from ( date_trunc('month', $1::TIMESTAMP) + interval '1 day' ) )::integer + 1
END
$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 100;
Example:week_no('2017-01-01') = 1
示例：week_no('2017-01-01') = 1
回答by Leo Fazzi
Maybe late.. but this works too
也许晚了..但这也有效
where cast(call_start as Date) = Date 'today'
where cast(call_start as Date) = Date '今天'
or cast(call_start as Date)= Date 'today' -1
或 cast(call_start as Date)= Date 'today' -1
or cast(call_start as Date)= Date 'today' -2
或 cast(call_start as Date)= Date 'today' -2
or cast(call_start as Date)= Date 'today' -3
或 cast(call_start as Date)= Date 'today' -3
or cast(call_start as Date)= Date 'today' -4
或 cast(call_start as Date)= Date 'today' -4
or cast(call_start as Date)= Date 'today' -5
或 cast(call_start as Date)= Date 'today' -5
or cast(call_start as Date)= Date 'today' -6
或 cast(call_start as Date)= Date 'today' -6
or cast(call_start as Date)= Date 'today' -7
或 cast(call_start as Date)= Date 'today' -7
;
;
this gives me it
这给了我
