Thread: Extract week from date, start with sunday

Hi,


I am currently extracting my weeks from a date like this..

SELECT INTO extractweek EXTRACT( week FROM ldate );

This works fine, except that this assumes (according
to ISO standard) that weeks starts with Monday.

I need the weeks to start with Sunday instead, otherwise
I will not get the correct week.

Is there a workaround for this?

Thanks!


Kind Regards,
Robert

am  07.11.2005, um 14:47:35 +0100 mailte Robert Blixt folgendes:
> 
>  Hi,
> 
> 
> I am currently extracting my weeks from a date like this..
> 
> SELECT INTO extractweek EXTRACT( week FROM ldate );
> 
> This works fine, except that this assumes (according
> to ISO standard) that weeks starts with Monday.
> 
> I need the weeks to start with Sunday instead, otherwise
> I will not get the correct week.
> 
> Is there a workaround for this?

Add 1 day to ldate.

test=# select to_char('2005-11-05'::date+'1 day'::interval, 'IW');to_char
---------44
(1 row)

test=# select to_char('2005-11-06'::date+'1 day'::interval, 'IW');to_char
---------45
(1 row)


HTH, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    === 

> -----Ursprungligt meddelande-----
> Från: Janning Vygen [mailto:vygen@planwerk6.de]
> Skickat: den 7 november 2005 15:18
> Till: pgsql-sql@postgresql.org
> Kopia: Robert Blixt
> Ämne: Re: [SQL] Extract week from date, start with sunday
>
> Am Montag, 7. November 2005 14:47 schrieb Robert Blixt:
> >  Hi,
> >
> >
> > I am currently extracting my weeks from a date like this..
> >
> > SELECT INTO extractweek EXTRACT( week FROM ldate );
> >
> > This works fine, except that this assumes (according
> > to ISO standard) that weeks starts with Monday.
> >
> > I need the weeks to start with Sunday instead, otherwise
> > I will not get the correct week.
> >
> > Is there a workaround for this?
>
> calculation of weeks is not easy, but it should work like this to get a
> non-ISO week number:
>
> SELECT INTO extractweek EXTRACT( week FROM (ldate + '1 day'::interval));
>
> But its is just a guess. Because i dont know anything about your non-ISO
> rules. In which week is 2006-01-01? ISO Week 52/2005. Why dont you just
> take
> ISO Weeks. It's a standard. That's a good thing.
>
> kind regards
> Janning
>
> --
> PLANWERK 6 websolutions
> Herzogstraße 85, 40215 Düsseldorf
> Tel.: 0211-6015919 Fax: 0211-6015917
> http://www.planwerk6.de/


Thanks everyone for your help, adding a day
to the date does seem to do the trick.

I would certainly prefer to use the ISO standard.
However, the business sector that we develop for
use their standard weeks from Sun - Sat, so it isn't
really an option for us.

Thanks again everyone.


Kind Regards,
Robert

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

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 ;)

There are some tools.. search about Ora2PG or so

One other way to go is to have the Oracle DB dumped out as text and
change/review the statements to suit your new structure and PG
differences
You definitely need mass changing tools with regular expressions.


On 11/9/05, zenith Das <zenith_das@yahoo.com> wrote:
>
> Hi
>  Can anyone help me out........
> How can i migrate database from oracle9i to postgreSQL8.0.3 where the table
> structure may differ in certain way...............
>
>

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.

You can use the below query to get the week from Sunday. 

select ceil(((now()::date -((CAST(EXTRACT (year FROM  now()) as
text)||'-01-04')::date - CAST            (EXTRACT (isodow FROM  (CAST(EXTRACT (year FROM  now()) as
text)||'-01-04')::date)            as integer)            ))+1)/7.0)


While using it you need to replace the now() with the date value you want to
compute the week number.

Explanation: 
1)(no of days between the date provided and first day of first week of the
year +1)/7.0
First week of the year contains 4th January (refer Note).
So first day of first week = first day of the week in which 4th January
belongs.
2)Finally take the ceiling of the result.


Note: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. 

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Extract-week-from-date-start-with-sunday-tp2144144p4892306.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.