Jaime Casanova wrote:
> On 11/8/05, lucas@presserv.org <lucas@presserv.org> wrote:
>
>>Hi
>>Looking the e-mail I remembered a question.
>>I saw that "select extract (week from now()::date)" will return the
>>week number
>>of current year. But, how can I convert a week to the first reference
>>date. Ex:
>>select extract(week from '20050105'::date); -- 5 Jan 2005
>>--Returns--
>>date_part |
>>1 |
>>
>>It is the first week of year (2005), and how can I get what is the first date
>>references the week 1? Ex:
>>select ???? week 1
>>--should return---
>>date |
>>20050103 | -- 3 Jan 2005
>>
>>Thank you.
>>Lucas Vendramin
>>
>>
>
>
> Extracted from:
> http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
>
> --- begin extracted text ---
>
> week
> The number of the week of the year that the day is in. By definition
> (ISO 8601), the first week of a year contains January 4 of that year.
> (The ISO-8601 week starts on Monday.) In other words, the first
> Thursday of a year is in week 1 of that year. (for timestamp values
> only)
>
> Because of this, it is possible for early January dates to be part of
> the 52nd or 53rd week of the previous year. For example, 2005-01-01 is
> part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd
> week of year 2005.
>
> SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
> Result: 7
>
> --- end extracted text ---
>
> --
> regards,
> Jaime Casanova
> (DBA: DataBase Aniquilator ;)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
Hi
http://www.postgresql.org/docs/8.0/static/functions-formatting.html
for the first week of 2005 use
=> select to_timestamp('1 2005','IW YYYY')::date as week_start; week_start
------------ 2005-01-03
Regards Neil.
