Get current week in postgreSQL

Question

I have been searching the web for the proper postgreSQL syntax for current_week. I searched through the link attached but could not get anything fruition out of it Date/Time. My task is to get Sunday as the start of the week.

I tried same as current_date but it failed:

select current_week

There has to be a current week syntax for postgreSQL.

Answer 1

knowing that for extract('dow' from

The day of the week as Sunday (0) to Saturday (6)

and

By definition, ISO weeks start on Mondays

You can workaround by substracting one day:

select date_trunc('week', current_date) - interval '1 day' as current_week
  current_week
------------------------
 2016-12-18 00:00:00+00
(1 row)

Here is sample:

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

Answer 2

One method would be date_trunc():

select date_trunc('week', current_date) as current_week