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.

Solution 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

Solution 2

One method would be date_trunc():

select date_trunc('week', current_date) as current_week

Comments

[Jacob_Cortese]

Thanks Gordon, this shows Dec 19th as the current week. Is there anyway I can have to show Sunday, Dec 18th as the current week since the week starts on a Sunday.

[Sami Kuhmonen]

@PythonLearner You can always subtract a day off it if you want it to be a Sunday.

[Jacob_Cortese]

select date_trunc('week', current_date - 1) as current_week ....... this still shows Dec 19th.

[Vao Tsun]

that's funny - select extract('dow' from now()) shows day 2 and weeks start with day 0 so it starts with 18th

[Vao Tsun]

` select extract('dow' from '2016-12-18'::date);` gives 0 => 18 is start of week - right?..

[pozs]

extract(isodow from ...) is in sync with date_trunc('week', ...) -- extract(dow from ...) is the legacy behavior.

[Vao Tsun]

I guess @PythonLearner wants "week as dow" while week in fact behaves like "isoweek" if it would exist

[Jacob_Cortese]

Thanks Vao, is there another alternative of getting this done. This indeed is a lengthy process just to get Sunday as the start of the week.

[Vao Tsun]

well - I just rephrased your select date_trunc('week', current_date - 1) as current_week to select date_trunc('week', current_date) - interval '1 day' as current_week