我一直在搜索适用于current_week的正确的PostgreSQL语法。我查看了附加链接,但没有找到有用的信息:日期/时间。我的任务是将周日作为一周的开始。
我尝试使用与current_date相同的方法,但失败了:
我尝试使用与current_date相同的方法,但失败了:
select current_week
PostgreSQL需要一个当前周的语法。
select current_week
PostgreSQL需要一个当前周的语法。
了解到对于extract('dow' from
星期天(0)到星期六(6)的一周中的日子
并且
根据定义,ISO周从星期一开始
您可以通过减去一天来解决:
select date_trunc('week', current_date) - interval '1 day' as current_week
current_week
------------------------
2016-12-18 00:00:00+00
(1 row)
以下是样例:
t=# with d as (select generate_series('2016-12-11','2016-12-28','1 day'::interval) t)
select date_trunc('week', d.t)::date - interval '1 day' as current_week, extract('dow' from d.t), d.t from d
;
current_week | date_part | t
---------------------+-----------+------------------------
2016-12-04 00:00:00 | 0 | 2016-12-11 00:00:00+00
2016-12-11 00:00:00 | 1 | 2016-12-12 00:00:00+00
2016-12-11 00:00:00 | 2 | 2016-12-13 00:00:00+00
2016-12-11 00:00:00 | 3 | 2016-12-14 00:00:00+00
2016-12-11 00:00:00 | 4 | 2016-12-15 00:00:00+00
2016-12-11 00:00:00 | 5 | 2016-12-16 00:00:00+00
2016-12-11 00:00:00 | 6 | 2016-12-17 00:00:00+00
2016-12-11 00:00:00 | 0 | 2016-12-18 00:00:00+00
2016-12-18 00:00:00 | 1 | 2016-12-19 00:00:00+00
2016-12-18 00:00:00 | 2 | 2016-12-20 00:00:00+00
2016-12-18 00:00:00 | 3 | 2016-12-21 00:00:00+00
2016-12-18 00:00:00 | 4 | 2016-12-22 00:00:00+00
2016-12-18 00:00:00 | 5 | 2016-12-23 00:00:00+00
2016-12-18 00:00:00 | 6 | 2016-12-24 00:00:00+00
2016-12-18 00:00:00 | 0 | 2016-12-25 00:00:00+00
2016-12-25 00:00:00 | 1 | 2016-12-26 00:00:00+00
2016-12-25 00:00:00 | 2 | 2016-12-27 00:00:00+00
2016-12-25 00:00:00 | 3 | 2016-12-28 00:00:00+00
(18 rows)
Time: 0.483 ms
select date_trunc('week', current_date - 1) as current_week
改写为 select date_trunc('week', current_date) - interval '1 day' as current_week
。 - Vao Tsun一种方法是使用 date_trunc()
函数:
select date_trunc('week', current_date) as current_week
select extract('dow' from now())
显示星期二,而且一周的开始是从第0天开始的,所以它从18号开始。 - Vao Tsun