pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExtract date from week
3+ messages / 3 participants
[nested] [flat]
* Extract date from week
@ 2005-11-08 11:25 lucas@presserv.org
2005-11-08 19:01 ` Re: Extract date from week Jaime Casanova <systemguards@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: lucas@presserv.org @ 2005-11-08 11:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Extract date from week
2005-11-08 11:25 Extract date from week lucas@presserv.org
@ 2005-11-08 19:01 ` Jaime Casanova <systemguards@gmail.com>
2005-12-15 01:08 ` Re: Extract date from week Neil Dugan <postgres@butterflystitches.com.au>
0 siblings, 1 reply; 3+ messages in thread
From: Jaime Casanova @ 2005-11-08 19:01 UTC (permalink / raw)
To: lucas@presserv.org <lucas@presserv.org>; +Cc: pgsql-sql
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 ;)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Extract date from week
2005-11-08 11:25 Extract date from week lucas@presserv.org
2005-11-08 19:01 ` Re: Extract date from week Jaime Casanova <systemguards@gmail.com>
@ 2005-12-15 01:08 ` Neil Dugan <postgres@butterflystitches.com.au>
0 siblings, 0 replies; 3+ messages in thread
From: Neil Dugan @ 2005-12-15 01:08 UTC (permalink / raw)
To: Jaime Casanova <systemguards@gmail.com>; +Cc: lucas@presserv.org <lucas@presserv.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-12-15 01:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-08 11:25 Extract date from week lucas@presserv.org
2005-11-08 19:01 ` Jaime Casanova <systemguards@gmail.com>
2005-12-15 01:08 ` Neil Dugan <postgres@butterflystitches.com.au>
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