pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExtract week from date, start with sunday
4+ messages / 3 participants
[nested] [flat]
* Extract week from date, start with sunday
@ 2005-11-07 13:47 Robert Blixt <robert.blixt@transpa.se>
2005-11-07 14:27 ` Re: Extract week from date, start with sunday A. Kretschmer <andreas.kretschmer@schollglas.com>
2011-10-11 16:19 ` Re: Extract week from date, start with sunday skdangi <skdangi@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Robert Blixt @ 2005-11-07 13:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Extract week from date, start with sunday
2005-11-07 13:47 Extract week from date, start with sunday Robert Blixt <robert.blixt@transpa.se>
@ 2005-11-07 14:27 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 0 replies; 4+ messages in thread
From: A. Kretschmer @ 2005-11-07 14:27 UTC (permalink / raw)
To: pgsql-sql
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 ===
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Extract week from date, start with sunday
2005-11-07 13:47 Extract week from date, start with sunday Robert Blixt <robert.blixt@transpa.se>
@ 2011-10-11 16:19 ` skdangi <skdangi@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: skdangi @ 2011-10-11 16:19 UTC (permalink / raw)
To: pgsql-sql
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.h...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Extract week from date, start with sunday
@ 2005-11-08 06:52 Robert Blixt <robert.blixt@transpa.se>
0 siblings, 0 replies; 4+ messages in thread
From: Robert Blixt @ 2005-11-08 06:52 UTC (permalink / raw)
To: 'Janning Vygen' <vygen@planwerk6.de>; pgsql-sql
> -----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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-10-11 16:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-07 13:47 Extract week from date, start with sunday Robert Blixt <robert.blixt@transpa.se>
2005-11-07 14:27 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2011-10-11 16:19 ` skdangi <skdangi@gmail.com>
2005-11-08 06:52 Re: Extract week from date, start with sunday Robert Blixt <robert.blixt@transpa.se>
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