From: Neil Dugan <postgres@butterflystitches.com.au>
To: Jaime Casanova <systemguards@gmail.com>
Cc: lucas@presserv.org <lucas@presserv.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Extract date from week
Date: Thu, 15 Dec 2005 12:08:16 +1100
Message-ID: <43A0C200.7040103@butterflystitches.com.au> (raw)
In-Reply-To: <c2d9e70e0511081101l350ed586x11532f6709f3215c@mail.gmail.com>
References: <20051108065256.7DFE7D7971@svr1.postgresql.org>
<20051108082511.06n26rf2fc0gk8cg@www.presserv.org>
<c2d9e70e0511081101l350ed586x11532f6709f3215c@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgres@butterflystitches.com.au, systemguards@gmail.com, lucas@presserv.org
Subject: Re: Extract date from week
In-Reply-To: <43A0C200.7040103@butterflystitches.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox