Re: [SQL] Week to date function

[Jorge Godoy]

Are you sure?

http://en.wikipedia.org/wiki/ISO_8601

Week dates

Main article: ISO week date 

 *-Www* *or* *Www*  *-Www-D* *or* *WwwD*

Week date representations are in the format as shown in the box to the
right. [] indicates the *ISO week-numbering year* which is slightly
different to the calendar year (see below). [Www] is the *week
number*prefixed by the letter 'W', from W01 through W53. [D] is the
*weekday number*, from 1 through 7, beginning with Monday and ending with
Sunday. This form is popular in the manufacturing industries.

There are mutually equivalent descriptions of week 01:


   - the week with the year's first Thursday in it (the formal ISO
 definition),
 - the week with 4 January in it,
 - the first week with the majority (four or more) of its days in
 the starting year, and
 - the week starting with the Monday in the period 29 December – 4
 January.

*If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week
01.* If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53
of the previous year (there is no week 00). 28 December is always in the
last week of its year.

The week number can be described by counting the Thursdays: week 12 contains
the 12th Thursday of the year.


In 2009, January 1st. happened on a Thursday.

As Jan 1st, 2010 happened on a Friday, it was on week 53 of 2009.




--
Jorge Godoy 


On Fri, Mar 26, 2010 at 07:48, Ireneusz Pluta  wrote:

>
> Yes, much smarter.
> However, would give the same results on (year=2009, week=53) and
> (year=2010, week=1). In fact, 2009 did not have week 53.
> I wrapped it into a function with additional isoyear check and now seems
> OK.
>
> Thanks
>

Re: [SQL] Week to date function

[Ireneusz Pluta]

Jorge Godoy pisze:

Are you sure?

http://en.wikipedia.org/wiki/ISO_8601

snip


As Jan 1st, 2010 happened on a Friday, it was on week 53 of 2009.

you are right, thanks for pointing that out.

I didn't check the opposite way like this:

select date_part('week', '2010-01-01'::date);
date_part
---
   53

I need to recheck my code.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Week to date function

[Jorge Godoy]

It isn't.  The norm is defined by ISO 8601.

There is a lot of "fun" into date calculations.

The rule is to counts the number of Thursdays on a year to get the correct
number of weeks.

What bothers me more is that here it is cultural that weeks start on Sunday,
but ISO 8601 says they start on Monday.  In Portuguese, the word for Monday
can also be translated as "Second", as in "Second day". How to say that the
second day is in fact the first? :-)

--
Jorge Godoy 


On Sat, Mar 27, 2010 at 18:31, Hiltibidal, Rob <
rob.hiltibi...@argushealth.com> wrote:

> U only 52 calendar weeks in a year... I'm almost sure that is the
> norm
>
>