Quantcast

Extract week from date, start with sunday

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Extract week from date, start with sunday

robertblixt

 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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Extract week from date, start with sunday

A. Kretschmer
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    ===

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Extract week from date, start with sunday

robertblixt
In reply to this post by robertblixt


> -----Ursprungligt meddelande-----
> Från: Janning Vygen [mailto:[hidden email]]
> Skickat: den 7 november 2005 15:18
> Till: [hidden email]
> 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



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Extract date from week

Lucas-21
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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Extract date from week

Jaime Casanova
On 11/8/05, [hidden email] <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

migratation of database from oracle9i to postgreSQL8.0.3

zenith Das
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 <[hidden email]> wrote:
On 11/8/05, [hidden email] 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


Yahoo! FareChase - Search multiple travel sites in one click.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: migratation of database from oracle9i to postgreSQL8.0.3

Samer Abukhait
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 <[hidden email]> 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...............
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Extract date from week

Neil Dugan
In reply to this post by Jaime Casanova
Jaime Casanova wrote:

> On 11/8/05, [hidden email] <[hidden email]> 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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Extract week from date, start with sunday

skdangi
In reply to this post by robertblixt
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.

Loading...