From ipluta@wp.pl Thu Mar 25 09:26:50 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D0D0163240A for ; Thu, 25 Mar 2010 06:26:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64456-05-2 for ; Thu, 25 Mar 2010 09:26:40 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mx4.wp.pl (mx4.wp.pl [212.77.101.8]) by mail.postgresql.org (Postfix) with ESMTP id 6E973633837 for ; Thu, 25 Mar 2010 06:25:45 -0300 (ADT) Received: (wp-smtpd smtp.wp.pl 7508 invoked from network); 25 Mar 2010 10:25:43 +0100 Received: from abbl251.neoplus.adsl.tpnet.pl (HELO [127.0.0.1]) (ipluta@[83.6.201.251]) (envelope-sender ) by smtp.wp.pl (WP-SMTPD) with AES256-SHA encrypted SMTP for ; 25 Mar 2010 10:25:43 +0100 Message-ID: <4BAB2C15.8090406@wp.pl> Date: Thu, 25 Mar 2010 10:25:41 +0100 From: Ireneusz Pluta User-Agent: Thunderbird 2.0.0.24 (Windows/20100228) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Week to date function Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit X-WP-AV: skaner antywirusowy poczty Wirtualnej Polski S. A. X-WP-SPAM: NO 0000000 [MaMk] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.459 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457 X-Spam-Level: * X-Archive-Number: 201003/182 X-Sequence-Number: 33889 Hello,

is there any standard function, or a concise solution based on set of them, returning a set of dates included in a week of given year and week number?
I ended up with creating my own function as in the example below, but I am curious if I am not opening an open door.

Thanks

Irek.

CREATE OR REPLACE FUNCTION week2date(double precision, double precision) RETURNS SETOF date
AS
$_$
SELECT day
FROM    (
    SELECT to_char(day, 'IYYY')::integer    AS iyyy,
            to_char(day, 'IW' )::integer    AS iw,
            day
    FROM    (
        SELECT start + generate_series(0, n)   AS day
        FROM    (
            SELECT start,
                    (stop - start)::integer AS n
            FROM    (
                SELECT (to_date($1::text, 'YYYY'::text) - interval        '3 days')::date AS   start,
                        (to_date($1::text, 'YYYY'::text) + interval '1 year 3 days')::date AS   stop
            )       ss
        )       aa
    )       bb
)       cc
WHERE   iw = $2 AND iyyy = $1
ORDER
BY      day
$_$
LANGUAGE SQL
IMMUTABLE
;

SELECT week2date(date_part('year', now()), date_part('week', now()));
week2date
------------
2010-03-22
2010-03-23
2010-03-24
2010-03-25
2010-03-26
2010-03-27
2010-03-28
(7 rows)

SELECT week2date(2009, 53);
week2date
------------
2009-12-28
2009-12-29
2009-12-30
2009-12-31
2010-01-01
2010-01-02
2010-01-03
(7 rows)

SELECT week2date(2010, 1);
week2date
------------
2010-01-04
2010-01-05
2010-01-06
2010-01-07
2010-01-08
2010-01-09
2010-01-10
(7 rows)

From gray.ru@gmail.com Thu Mar 25 09:45:10 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C0C70632817 for ; Thu, 25 Mar 2010 06:45:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 66744-04 for ; Thu, 25 Mar 2010 09:44:59 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-wy0-f174.google.com (mail-wy0-f174.google.com [74.125.82.174]) by mail.postgresql.org (Postfix) with ESMTP id 17773632815 for ; Thu, 25 Mar 2010 06:44:59 -0300 (ADT) Received: by wyb38 with SMTP id 38so3377465wyb.19 for ; Thu, 25 Mar 2010 02:44:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type :content-transfer-encoding; bh=SVtwIgSUjolleIceAniMAT1gi6sIDUo+SuxyVbcjKHM=; b=Tiwsj1aFqbkQ+xioaUsZQvpB/5UeBs1SWafjpvQdKy2IXgAtszjWXYue/aO8e+QMEn U245ek+kQW1G7TuHdC9oXamlj7li+B0AXqbRZh/0zXpkONxrjBqOxIkidHHLcKKJ3CeT c5cENRm9D16waLXQVQZaiReOj6VWhVR2418gM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; b=feyH5N5W4EEnLr3b4eZE7ww0MmXHMFSXcANWNmDcO9STDZzMHg6ntT84D++P1nhkwY ozuKO0AlS8TGTwMEBDgwnR+EE1SxIdGHSaFTStKJdqizi7pEr5J9Ef23HHWrGfgg9G9H OKIpjUHB7zPthkQwI0lFjOfFGI9XbsmyzmpTo= MIME-Version: 1.0 Received: by 10.216.89.193 with SMTP id c43mr5542041wef.221.1269510298177; Thu, 25 Mar 2010 02:44:58 -0700 (PDT) In-Reply-To: <4BAB2C15.8090406@wp.pl> References: <4BAB2C15.8090406@wp.pl> From: Sergey Konoplev Date: Thu, 25 Mar 2010 12:44:38 +0300 Message-ID: Subject: Re: Week to date function To: Ireneusz Pluta Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 201003/183 X-Sequence-Number: 33890 On 25 March 2010 12:25, Ireneusz Pluta wrote: > Hello, > > is there any standard function, or a concise solution based on set of the= m, > returning a set of dates included in a week of given year and week number= ? > I ended up with creating my own function as in the example below, but I a= m > curious if I am not opening an open door. Try to think of something like this? SELECT date_trunc('week', '2010-01-01'::date) + '12 week'::interval + (d::text||' day')::interval FROM generate_series(0, 6) AS d; > > Thanks > > Irek. > > CREATE OR REPLACE FUNCTION week2date(double precision, double precision) > RETURNS SETOF date > AS > $_$ > SELECT=A0 day > FROM=A0=A0=A0 ( > =A0=A0=A0 SELECT=A0 to_char(day, 'IYYY')::integer=A0=A0=A0 AS=A0 iyyy, > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 to_char(day, 'IW'=A0 )::integer=A0=A0= =A0 AS=A0 iw, > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 day > =A0=A0=A0 FROM=A0=A0=A0 ( > =A0=A0=A0=A0=A0=A0=A0 SELECT=A0 start + generate_series(0, n)=A0=A0 AS=A0= day > =A0=A0=A0=A0=A0=A0=A0 FROM=A0=A0=A0 ( > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 SELECT=A0 start, > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (stop - start):= :integer AS=A0 n > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 FROM=A0=A0=A0 ( > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 SELECT=A0 (to_date($1::text= , 'YYYY'::text) - interval > '3 days')::date AS=A0=A0 start, > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (to= _date($1::text, 'YYYY'::text) + interval '1 year > 3 days')::date AS=A0=A0 stop > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 )=A0=A0=A0=A0=A0=A0 ss > =A0=A0=A0=A0=A0=A0=A0 )=A0=A0=A0=A0=A0=A0 aa > =A0=A0=A0 )=A0=A0=A0=A0=A0=A0 bb > )=A0=A0=A0=A0=A0=A0 cc > WHERE=A0=A0 iw =3D $2 AND iyyy =3D $1 > ORDER > BY=A0=A0=A0=A0=A0 day > $_$ > LANGUAGE SQL > IMMUTABLE > ; > > SELECT week2date(date_part('year', now()), date_part('week', now())); > =A0week2date > ------------ > =A02010-03-22 > =A02010-03-23 > =A02010-03-24 > =A02010-03-25 > =A02010-03-26 > =A02010-03-27 > =A02010-03-28 > (7 rows) > > SELECT week2date(2009, 53); > =A0week2date > ------------ > =A02009-12-28 > =A02009-12-29 > =A02009-12-30 > =A02009-12-31 > =A02010-01-01 > =A02010-01-02 > =A02010-01-03 > (7 rows) > > SELECT week2date(2010,=A0 1); > =A0week2date > ------------ > =A02010-01-04 > =A02010-01-05 > =A02010-01-06 > =A02010-01-07 > =A02010-01-08 > =A02010-01-09 > =A02010-01-10 > (7 rows) > > --=20 Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802 From ipluta@wp.pl Fri Mar 26 10:48:57 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id DE6E263273B for ; Fri, 26 Mar 2010 07:48:57 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 15607-06 for ; Fri, 26 Mar 2010 10:48:47 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mx4.wp.pl (mx4.wp.pl [212.77.101.8]) by mail.postgresql.org (Postfix) with ESMTP id 311A3632636 for ; Fri, 26 Mar 2010 07:48:46 -0300 (ADT) Received: (wp-smtpd smtp.wp.pl 8941 invoked from network); 26 Mar 2010 11:48:44 +0100 Received: from aazd173.neoplus.adsl.tpnet.pl (HELO [127.0.0.1]) (ipluta@[83.6.141.173]) (envelope-sender ) by smtp.wp.pl (WP-SMTPD) with AES256-SHA encrypted SMTP for ; 26 Mar 2010 11:48:44 +0100 Message-ID: <4BAC9107.5070701@wp.pl> Date: Fri, 26 Mar 2010 11:48:39 +0100 From: Ireneusz Pluta User-Agent: Thunderbird 2.0.0.24 (Windows/20100228) MIME-Version: 1.0 To: Sergey Konoplev CC: pgsql-sql@postgresql.org Subject: Re: Week to date function References: <4BAB2C15.8090406@wp.pl> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-WP-AV: skaner antywirusowy poczty Wirtualnej Polski S. A. X-WP-SPAM: NO 0000000 [0SO0] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.74 tagged_above=-10 required=5 tests=BAYES_20=-0.74 X-Spam-Level: X-Archive-Number: 201003/204 X-Sequence-Number: 33911 Sergey Konoplev pisze: > On 25 March 2010 12:25, Ireneusz Pluta wrote: > >> Hello, >> >> is there any standard function, or a concise solution based on set of them, >> returning a set of dates included in a week of given year and week number? >> I ended up with creating my own function as in the example below, but I am >> curious if I am not opening an open door. >> > > Try to think of something like this? > > SELECT > date_trunc('week', '2010-01-01'::date) + > '12 week'::interval + > (d::text||' day')::interval > FROM generate_series(0, 6) AS d; > > Yes, much smarter. However, would give the same results on (year=2009, week=53) and (year=2010, week=1). In fact, 2009 did not have week 53. I wrapped it into a function with additional isoyear check and now seems OK. Thanks CREATE OR REPLACE FUNCTION your_week2date(double precision, double precision) RETURNS SETOF date AS $_$ SELECT day FROM ( SELECT ( date_trunc('week', ($1::text||'-01-01')::date) + ($2::text||' week')::interval + ( d::text||' day')::interval )::date AS day FROM generate_series(0, 6) AS d ) alias WHERE to_char(day, 'IYYY')::integer = $1 ORDER BY 1 $_$ LANGUAGE SQL IMMUTABLE ; SELECT week2date1(date_part('year', now()), date_part('week', now())); week2date1 ------------ 2010-03-22 2010-03-23 2010-03-24 2010-03-25 2010-03-26 2010-03-27 2010-03-28 (7 rows) SELECT your_week2date(2009, 52) ; your_week2date ---------------- 2009-12-28 2009-12-29 2009-12-30 2009-12-31 2010-01-01 2010-01-02 2010-01-03 (7 rows) SELECT your_week2date(2009, 53) ; your_week2date ---------------- (0 rows) SELECT your_week2date(2010, 1) ; your_week2date ---------------- 2010-01-04 2010-01-05 2010-01-06 2010-01-07 2010-01-08 2010-01-09 2010-01-10 (7 rows) From gray.ru@gmail.com Fri Mar 26 11:02:34 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A2D396327A2 for ; Fri, 26 Mar 2010 08:02:34 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 17495-08 for ; Fri, 26 Mar 2010 11:02:23 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com [74.125.82.46]) by mail.postgresql.org (Postfix) with ESMTP id 01F986323B7 for ; Fri, 26 Mar 2010 08:02:23 -0300 (ADT) Received: by wwe15 with SMTP id 15so6526634wwe.19 for ; Fri, 26 Mar 2010 04:02:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:received:message-id:subject:to:cc:content-type :content-transfer-encoding; bh=OIDIuZm7GAlG6CBe80HjMFX1ZRBh6kt2/sppKLctsuE=; b=WYf/KMyHWHZuVci//a1f31ADpylFcU5LtKQlq4/Rax1wCqXzDRYQFqYL/2kA3NovEn FZgS7Y5sGOo8bLgZjbPeLxWXxym0cH2pmBVOULIgzGtIqkcj9i6N2dwVduXITHLNFPb+ TwnkpG1tS0a+nml2nx39Ul4BXc+RdPHinHxb8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; b=q0g2bAco70qM5El9MI+nd5WCzo//fPEKRVSFZF2XZUSOFNUPBCX3CVNJWQR4X5vqjL 6oqJyJsF80aws5GwJMaUQi3+SyjUgzf9GZZ5V31qHXccVL7KvvEd9wuOAa7ibEfh5Bxn B133MUO78bwblCHh11URb9LTXTEM15OCQLrq0= MIME-Version: 1.0 Received: by 10.216.153.136 with HTTP; Fri, 26 Mar 2010 04:02:02 -0700 (PDT) In-Reply-To: <4BAC9107.5070701@wp.pl> References: <4BAB2C15.8090406@wp.pl> <4BAC9107.5070701@wp.pl> From: Sergey Konoplev Date: Fri, 26 Mar 2010 14:02:02 +0300 Received: by 10.216.160.213 with SMTP id u63mr354285wek.128.1269601342141; Fri, 26 Mar 2010 04:02:22 -0700 (PDT) Message-ID: Subject: Re: Week to date function To: Ireneusz Pluta Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/205 X-Sequence-Number: 33912 > CREATE OR REPLACE FUNCTION your_week2date(double precision, double > precision) RETURNS SETOF date > AS > $_$ > SELECT =A0day > FROM ( > =A0 SELECT =A0( > =A0 =A0 =A0 =A0 =A0 =A0 =A0 date_trunc('week', ($1::text||'-01-01')::date= ) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 + ($2::text||' week')::interval > =A0 =A0 =A0 =A0 =A0 =A0 =A0 + ( d::text||' =A0day')::interval > =A0 =A0 =A0 =A0 =A0 )::date =A0AS =A0day > =A0 FROM =A0 =A0generate_series(0, 6) AS d > ) =A0 alias > WHERE =A0 to_char(day, 'IYYY')::integer =3D $1 > ORDER > BY =A0 =A0 =A01 > $_$ > LANGUAGE SQL > IMMUTABLE I think it is better to use date_part('year', day) instead of to_char(...). And may be it is worth to do raise exception when incorrect week specified. --=20 Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802 From ipluta@wp.pl Fri Mar 26 11:47:27 2010 Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 9C1D56335D5 for ; Fri, 26 Mar 2010 08:47:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 05636-10 for ; Fri, 26 Mar 2010 11:47:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mx4.wp.pl (mx4.wp.pl [212.77.101.8]) by mail.postgresql.org (Postfix) with ESMTP id 614646323CE for ; Fri, 26 Mar 2010 08:47:16 -0300 (ADT) Received: (wp-smtpd smtp.wp.pl 23323 invoked from network); 26 Mar 2010 12:47:14 +0100 Received: from aazd173.neoplus.adsl.tpnet.pl (HELO [127.0.0.1]) (ipluta@[83.6.141.173]) (envelope-sender ) by smtp.wp.pl (WP-SMTPD) with AES256-SHA encrypted SMTP for ; 26 Mar 2010 12:47:14 +0100 Message-ID: <4BAC9EC1.7010708@wp.pl> Date: Fri, 26 Mar 2010 12:47:13 +0100 From: Ireneusz Pluta User-Agent: Thunderbird 2.0.0.24 (Windows/20100228) MIME-Version: 1.0 To: Sergey Konoplev CC: pgsql-sql@postgresql.org Subject: Re: Week to date function References: <4BAB2C15.8090406@wp.pl> <4BAC9107.5070701@wp.pl> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-WP-AV: skaner antywirusowy poczty Wirtualnej Polski S. A. X-WP-SPAM: NO 0000000 [8VNk] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.299 tagged_above=-10 required=5 tests=AWL=1.300, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/206 X-Sequence-Number: 33913 Sergey Konoplev pisze: >> CREATE OR REPLACE FUNCTION your_week2date(double precision, double >> precision) RETURNS SETOF date >> AS >> $_$ >> SELECT day >> FROM ( >> SELECT ( >> date_trunc('week', ($1::text||'-01-01')::date) >> + ($2::text||' week')::interval >> + ( d::text||' day')::interval >> )::date AS day >> FROM generate_series(0, 6) AS d >> ) alias >> WHERE to_char(day, 'IYYY')::integer = $1 >> ORDER >> BY 1 >> $_$ >> LANGUAGE SQL >> IMMUTABLE >> > > I think it is better to use date_part('year', day) instead of > to_char(...). this might cut first or last week in the year, like this: SELECT your_week2date(2009, 52) ; your_week2date ---------------- 2009-12-28 2009-12-29 2009-12-30 2009-12-31 2010-01-01 cut 2010-01-02 cut 2010-01-03 cut (7 rows) which is not what I want when playing with isoweeks. > And may be it is worth to do raise exception when > incorrect week specified. > but, maybe, controlled by an additonal parameter saying if one prefers to have exception or rather an empty resultset From jgodoy@gmail.com Sat Mar 27 18:10:45 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 34BEB6347BB for ; Sat, 27 Mar 2010 15:10:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 22958-01-6 for ; Sat, 27 Mar 2010 18:10:34 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gy0-f174.google.com (mail-gy0-f174.google.com [209.85.160.174]) by mail.postgresql.org (Postfix) with ESMTP id 329726343D3 for ; Sat, 27 Mar 2010 15:09:14 -0300 (ADT) Received: by gyg13 with SMTP id 13so668270gyg.19 for ; Sat, 27 Mar 2010 11:09:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:received:message-id:subject:to:cc:content-type; bh=GHFwRpY3Xa1nL8I5lRjQGYvByH57JRPP6MIhjifdCQY=; b=gBc0mHHEutXPeSMtSW5pv4foq94fdLNrBknkTb5PCvAxe8lR1FQFqS2r+wcQ8QUdWi YIzIdT9cJpBxmbtLOeUSncVFqtDRMb9LXCnSqIbDdquEW9rj128KTjLX4Xr4AtkSbfcp g6SU7An5mdr6zrWhZjD4B/1rNBi5cVVxnMMVY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=aW+xGnL5Mt/CD/ITd30tpMUr0jnBH0meW8iwlWYjLksjlSaUXqDnG93Wtw6jyW1omL EkAgD+Tu+SrKHozMk38Wu8kQDkbTa2fqC64ub1t2VEk/lnb/mXy7rZKDu6dVHCfU5lGV jtfUqZ6MqQtPg++8ULSmG2ByoMtAzilXqoyVc= MIME-Version: 1.0 Received: by 10.100.253.19 with HTTP; Sat, 27 Mar 2010 11:08:53 -0700 (PDT) In-Reply-To: <4BAC9107.5070701@wp.pl> References: <4BAB2C15.8090406@wp.pl> <4BAC9107.5070701@wp.pl> From: Jorge Godoy Date: Sat, 27 Mar 2010 15:08:53 -0300 Received: by 10.100.22.17 with SMTP id 17mr2356715anv.241.1269713353154; Sat, 27 Mar 2010 11:09:13 -0700 (PDT) Message-ID: Subject: Re: Week to date function To: Ireneusz Pluta Cc: Sergey Konoplev , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001485f897cc61a10d0482cc2da8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/207 X-Sequence-Number: 33914 --001485f897cc61a10d0482cc2da8 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Are you sure? http://en.wikipedia.org/wiki/ISO_8601 Week dates Main article: ISO week date *YYYY-Www* *or* *YYYYWww* *YYYY-Www-D* *or* *YYYYWwwD* Week date representations are in the format as shown in the box to the right. [YYYY] indicates the *ISO week-numbering year* which is slightly different to the calendar year (see below). [Www] is the *week number*prefixed by the letter 'W', from W01 through W53. [D] is the *weekday number*, from 1 through 7, beginning with Monday and ending with Sunday. This form is popular in the manufacturing industries. There are mutually equivalent descriptions of week 01: - the week with the year's first Thursday in it (the formal ISO definition), - the week with 4 January in it, - the first week with the majority (four or more) of its days in the starting year, and - the week starting with the Monday in the period 29 December =96 = 4 January. *If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01.* If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 5= 3 of the previous year (there is no week 00). 28 December is always in the last week of its year. The week number can be described by counting the Thursdays: week 12 contain= s the 12th Thursday of the year. In 2009, January 1st. happened on a Thursday. As Jan 1st, 2010 happened on a Friday, it was on week 53 of 2009. -- Jorge Godoy On Fri, Mar 26, 2010 at 07:48, Ireneusz Pluta wrote: > > Yes, much smarter. > However, would give the same results on (year=3D2009, week=3D53) and > (year=3D2010, week=3D1). In fact, 2009 did not have week 53. > I wrapped it into a function with additional isoyear check and now seems > OK. > > Thanks > --001485f897cc61a10d0482cc2da8 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Are you sure?

http://en.wikipedia.org/wiki/ISO_8601=A0

Week dates

Main article: ISO week date

<= table class=3D"infobox" style=3D"text-align: center; width: 25em;"> YYYY-Www or YYYYWww YYYY-Www-D or YYYYWwwD

We= ek date representations are in the format as shown in the box to=20 the right. [YYYY] indicates the ISO week-numbering year which is=20 slightly different to the calendar year (see below). [Www] is the week number prefixed by the letter 'W', from W01 through W53. [D] i= s the weekday number, from 1 through 7, beginning with Monday and=20 ending with Sunday. This form is popular in the manufacturing=20 industries.

The= re are mutually equivalent descriptions of week 01:

the week with the year's first Thursday in it (the formal I= SO=20 definition),
the week with 4 January in it,
the first week = with the majority (four or more) of its days in the=20 starting year, and
the week starting with the Monday in the period = 29 December =96 4=20 January.

<= b>If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in wee= k 52 or 53 of the previous year (there is no week 00). 28 December is=20 always in the last week of its year.
The week number can be described by counting the Thur= sdays: week 12=20 contains the 12th Thursday of the year.

In 2009, January 1st. happened on a Thursday.

As Jan 1st, 2010 happened on a Friday, it was on week 53 of= 2009.

--
Jorge Godoy =A0 =A0 <jgodo= y@gmail.com>

On Fri, Mar 26, 2010 at 07:48, Ireneusz = Pluta <ipluta@wp.pl> wrote:

=A0

Yes, much smarter.
However, would give the same results on (year=3D2009, week=3D53) and (year= =3D2010, week=3D1). In fact, 2009 did not have week 53.
I wrapped it into a function with additional isoyear check and now seems OK= .

Thanks

There = is a lot of "fun" into date calculations.

The rule is to counts the number of Thursdays on a year to get the correc= t number of weeks.

What bothers me more is that here it is cultural that weeks = start on Sunday, but ISO 8601 says they start on Monday. =A0In Portuguese, = the word for Monday can also be translated as "Second", as in &qu= ot;Second day". How to say that the second day is in fact the first? := -)

--
Jorge Godoy =A0 =A0 <= jgodoy@gmail.com>

On Sat, Mar 27, 2010 at 18:31, Hiltibida= l, Rob <Rob.Hiltibidal@argushealth.com> wrote:

Uhhhh only 52 calendar weeks in a year... I'm almost sure that is the norm

--0016e6d27d44234c1c0482d2a5e6-- From jasen@xnet.co.nz Wed Mar 31 11:45:35 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 10C8A6333EA for ; Wed, 31 Mar 2010 08:45:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 29161-09 for ; Wed, 31 Mar 2010 11:45:24 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ananke.wxnz.net (ananke.wxnz.net [58.28.4.122]) by mail.postgresql.org (Postfix) with ESMTP id 75638632F75 for ; Wed, 31 Mar 2010 08:45:24 -0300 (ADT) Received: from gonzo (ip-118-90-134-42.xdsl.xnet.co.nz [118.90.134.42]) by ananke.wxnz.net (Postfix) with ESMTP id 8D6F3171134 for ; Thu, 1 Apr 2010 00:44:24 +1300 (NZDT) Received: from news by gonzo with local (Exim 4.69) (envelope-from ) id 1NwwLP-0003q6-40 for pgsql-sql@postgresql.org; Thu, 01 Apr 2010 00:44:23 +1300 To: pgsql-sql@postgresql.org Path: not-for-mail From: Jasen Betts Newsgroups: gmane.comp.db.postgresql.sql Subject: Re: Week to date function Date: 31 Mar 2010 11:44:17 GMT Organization: Dis (not Dat) Organisation Lines: 10 Message-ID: References: <4BAB2C15.8090406@wp.pl> <4BAC9107.5070701@wp.pl> <4BAE68FA.8000803@wp.pl> <40FB2AE5907F9743A593A85015F157BF03FE1086@ARG-EXVS03.corp.argushealth.com> NNTP-Posting-Host: localhost Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: reversiblemaps.ath.cx 1270035857 14583 127.0.0.1 (31 Mar 2010 11:44:17 GMT) X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W; (T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q; 8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^ User-Agent: slrn/pre1.0.0-2 (Linux) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 201003/219 X-Sequence-Number: 33926 On 2010-03-27, Hiltibidal, Rob wrote: > Uhhhh only 52 calendar weeks in a year... I'm almost sure that is the > norm All hours have 60 minutes All weeks have 7 days All years have 12 months all else is variable.