Getting the right week number of year

Question

I want to get the right week number.

Using the to_char function, the function enumerate the week starting with mondey, but i want to get the week number considering that a week start on saturday.

Here is an example:

SELECT to_char('05-01-2013'::date,'daydd-mm-yyyy') as date_char, to_char('05-01-2013'::date,'IW') as week_number

Result:

"saturday 05-01-2013";"01"

And should be:

"saturday 05-01-2013";"02"

Is there any way to get it?

Config info: Postgresql 9.2 under Windows 7

I don't understand your question. The select you give, doesn't match the result you give. 'SELECT to_char('05-01-2013'::date,'daydd-mm-yyyy') as date_char, to_char('05-01-2013'::date,'IW') as week_number;' will give you: 'wednesday01-05-2013';'18'. What result do you want it to give you? — David S, Feb 11 '13 at 16:17
Why wednesday ? the 05-01-2013 was saturday! I'am looking for telling to postgresql that the week start from saturday to friday, and not from monday to sunday. — Houari, Feb 11 '13 at 18:05
May 1st 2013 is going to be a Wednesday. If I cut/paste the query you have, I get the results I posted. I think there maybe a locale issue here. I think you should clarify what locale you are using and exactly what you are getting and what you expect. — David S, Feb 11 '13 at 18:16
Probably you could adapt the mysql answer to: [Week of the year for weeks starting with Saturday](http://stackoverflow.com/q/10656996/238814) — Daniel Vérité, Feb 11 '13 at 20:25

Lucas · Answer 1 · 2013-02-11T18:57:29.063

3

SELECT EXTRACT(WEEK FROM now()); will give the number 7, indicating that (at present) we are in the 7th week of the year. Postgres weeks start on Monday by default.

SELECT EXTRACT(WEEK FROM timestamp '2013-01-05'); will give 1 as only 5 days have passed since the start of the year.

Edit it appears that it only starts to count weeks from the time that the first full week has passed. Without writing your own function I would suggest going with this or using a different method to find it.

edited Feb 11 '13 at 18:57

answered Feb 11 '13 at 18:42

Lucas

1,476
13
20

Yes, i was wondering that should be un ready to use function, or somethins to configure. damm it – Houari Feb 11 '13 at 19:19

score 2 · Answer 2 · 2013-02-11T18:39:29.273

2

You'll have to use extract(doy from ...) and extract(dow from ...) plus some math. Details in documentation.

edited Feb 11 '13 at 18:39

answered Feb 11 '13 at 16:11

I am not looking for a day of week, but the week number of year – Houari Feb 11 '13 at 18:07
And the week number should considere that a week start from saturday to friday. – Houari Feb 11 '13 at 18:09
Should i write function to achieve this ? – Houari Feb 11 '13 at 19:17
You could. It would be pretty simple SQL function, and would simplify further queries. Or more exactly - make it more readable. – Feb 11 '13 at 20:46

Clodoaldo Neto · Accepted Answer · 2013-02-13T16:29:14.873

This will give the correct result:

with first_friday as (
    select extract(doy from min(a)::date) ff
    from generate_series('2013-01-01'::date, '2013-01-07', '1 day') s(a)
    where extract(dow from a) = 5
)
select
    to_char(a, 'day'),
    a::date "day",
    floor((extract(doy from a) - (select ff from first_friday) - 1) / 7) + 2 week_number
from generate_series('2013-01-01'::date, '2013-01-31', '1 day') s(a)
;
  to_char  |    day     | week_number 
-----------+------------+-------------
 tuesday   | 2013-01-01 |           1
 wednesday | 2013-01-02 |           1
 thursday  | 2013-01-03 |           1
 friday    | 2013-01-04 |           1
 saturday  | 2013-01-05 |           2
 sunday    | 2013-01-06 |           2
 monday    | 2013-01-07 |           2
 tuesday   | 2013-01-08 |           2
 wednesday | 2013-01-09 |           2
 thursday  | 2013-01-10 |           2
 friday    | 2013-01-11 |           2
 saturday  | 2013-01-12 |           3
 sunday    | 2013-01-13 |           3
 monday    | 2013-01-14 |           3
 tuesday   | 2013-01-15 |           3
 wednesday | 2013-01-16 |           3
 thursday  | 2013-01-17 |           3
 friday    | 2013-01-18 |           3
 saturday  | 2013-01-19 |           4
 sunday    | 2013-01-20 |           4
 monday    | 2013-01-21 |           4
 tuesday   | 2013-01-22 |           4
 wednesday | 2013-01-23 |           4
 thursday  | 2013-01-24 |           4
 friday    | 2013-01-25 |           4
 saturday  | 2013-01-26 |           5
 sunday    | 2013-01-27 |           5
 monday    | 2013-01-28 |           5
 tuesday   | 2013-01-29 |           5
 wednesday | 2013-01-30 |           5
 thursday  | 2013-01-31 |           5

No, it's not so simple, because 29-12-2012 to 31-12-2012 should be 53, and not 01. — Houari, Feb 13 '13 at 15:21
@Houari You mean the first day of the year should always be on week 1? Are you aware of the ISO definition in that the first week should include January 4th and because of that sometimes the first day will be on the 53th week? You should clearly state what your definition week-year is. — Clodoaldo Neto, Feb 13 '13 at 15:26
Yes, the first day of the year should always be on week number 1. — Houari, Feb 13 '13 at 15:28
The first week starts on the first day of the year, and the the first friday of the new year is in week 1. — Houari, Feb 13 '13 at 15:53
Yes! that's exactly what i want, but only for 2013 year, is it possible for all dates ? — Houari, Feb 13 '13 at 18:23

Getting the right week number of year

3 Answers3

Linked