Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YvuPr-0005Tl-EY for pgsql-performance@arkaria.postgresql.org; Fri, 22 May 2015 21:23:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YvuPq-00053x-SJ for pgsql-performance@arkaria.postgresql.org; Fri, 22 May 2015 21:23:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YvuPq-00053p-38 for pgsql-performance@postgresql.org; Fri, 22 May 2015 21:23:38 +0000 Received: from mail-by2on0066.outbound.protection.outlook.com ([207.46.100.66] helo=na01-by2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1YvuPi-0000Dc-1z for pgsql-performance@postgresql.org; Fri, 22 May 2015 21:23:37 +0000 Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.local (70.113.16.71) by BY2PR11MB0680.namprd11.prod.outlook.com (25.163.111.22) with Microsoft SMTP Server (TLS) id 15.1.166.22; Fri, 22 May 2015 21:23:25 +0000 Message-ID: <555F9E47.5020003@BlueTreble.com> Date: Fri, 22 May 2015 16:23:19 -0500 From: Jim Nasby User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:31.0) Gecko/20100101 Thunderbird/31.4.0 MIME-Version: 1.0 To: Bosco Rama , "David G. Johnston" , "er.tejaspatel88@gmail.com" CC: "pgsql-performance@postgresql.org" Subject: Re: Fastest way / best practice to calculate "next birthdays" References: <481DE036.90706@commandprompt.com> <481DF76D.3060807@theendofthetunnel.de> <481E9E12.8070208@Sheeky.Biz> <1431941415888-5849705.post@n5.nabble.com> <555E0488.1030908@boscorama.com> In-Reply-To: <555E0488.1030908@boscorama.com> Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [70.113.16.71] X-ClientProxiedBy: BLUPR05CA0078.namprd05.prod.outlook.com (10.141.20.48) To BY2PR11MB0680.namprd11.prod.outlook.com (25.163.111.22) X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BY2PR11MB0680; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(5005006)(520002)(3002001); SRVR:BY2PR11MB0680; BCL:0; PCL:0; RULEID:; SRVR:BY2PR11MB0680; X-Forefront-PRVS: 058441C12A X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(6009001)(24454002)(377454003)(199003)(189002)(479174004)(189998001)(40100003)(80316001)(122386002)(5001860100001)(86362001)(62966003)(77156002)(23676002)(50466002)(65816999)(19580395003)(33656002)(87976001)(101416001)(2501003)(83506001)(87266999)(50986999)(105586002)(106356001)(36756003)(93886004)(47776003)(59896002)(5001920100001)(2950100001)(42186005)(15975445007)(68736005)(5001830100001)(81156007)(54356999)(65956001)(76176999)(5001960100002)(64126003)(46102003)(97736004)(66066001)(5001770100001)(4001350100001)(4001540100001)(64706001)(92566002)(65806001)(15123001)(85282002)(336755003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:BY2PR11MB0680; H:decina.local; FPR:; SPF:None; PTR:InfoNoRecords; A:1; MX:1; LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 22 May 2015 21:23:25.0925 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY2PR11MB0680 X-Pg-Spam-Score: -3.0 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 5/21/15 11:15 AM, Bosco Rama wrote: > You could probably make some of that into a function that accepts a > timestamptz and generates the two days. You'll be better off if instead of 2 days it gives you a daterange: http://www.postgresql.org/docs/9.4/static/rangetypes.html I don't know about the exact ISO details, but your approach is the correct one: find the date that the current week started on and then build a range of [week start, week start + 7 days). Also, note the use of [ vs ). That is the ONLY correct way to do this if you're comparing to a timestamp. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance