Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gSpFx-0007sd-OB for pgsql-sql@arkaria.postgresql.org; Fri, 30 Nov 2018 20:19:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gSpFw-0006bz-Fx for pgsql-sql@arkaria.postgresql.org; Fri, 30 Nov 2018 20:19:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gSpFv-0006bs-QC for pgsql-sql@lists.postgresql.org; Fri, 30 Nov 2018 20:19:20 +0000 Received: from mx1.riseup.net ([198.252.153.129]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gSpFr-0007oW-Ih for pgsql-sql@lists.postgresql.org; Fri, 30 Nov 2018 20:19:18 +0000 Received: from cotinga.riseup.net (cotinga-pn.riseup.net [10.0.1.164]) (using TLSv1 with cipher ECDHE-RSA-AES256-SHA (256/256 bits)) (Client CN "*.riseup.net", Issuer "COMODO RSA Domain Validation Secure Server CA" (verified OK)) by mx1.riseup.net (Postfix) with ESMTPS id 5B9E01A27D1 for ; Fri, 30 Nov 2018 12:19:12 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=riseup.net; s=squak; t=1543609152; bh=Qk0uobSUVY7AfR8wdOMPpE681ValQiWrL6rd5Jh8W1s=; h=Subject:To:References:From:Date:In-Reply-To:From; b=ClTukDmKSF2qSWwUwgmRGEJ9XCuG/z4mfGu2j1nk2nD7SkRbLJ9Aj6B4uG97EmrfT GBY0W7DVcvQWAZsnBB0qH0Q/KMRZrN02qCBErhCNJVT3TakIco08Pj/4R681JQ5BNq tknFBNkwd+TqNvqLDktdcMXR+PA81dJC2bL71Z54= X-Riseup-User-ID: 1316F151D894C2A9373227458E5BAB73969A652B7D2C07DEE39DB25143C618AD Received: from [127.0.0.1] (localhost [127.0.0.1]) by cotinga.riseup.net with ESMTPSA id CF6E06EDA3 for ; Fri, 30 Nov 2018 12:19:11 -0800 (PST) Subject: Re: interval origami To: "pgsql-sql@lists.postgresql.org" References: <20181130175719.l7izv65wjwgkls35@alvherre.pgsql> From: Adam Jensen Openpgp: preference=signencrypt Autocrypt: addr=hanzer@riseup.net; keydata= xsFNBFITbtMBEADj0R/kXShGc0soC29c5WkR56N/nsQ/VgXExduZ3Agyu/5AintZDbcJ9pLO PsaYKJfchIa9Fv1lcLhaaGPyPBYTklU8axSDpDa2Y6Kc2Ezyjq6dDEdk3La2eWER8BDQHNP+ MYZ8Qin+tllQwX/uufC4UBvZxxIOhp05gAryvFPcXoq1MXrj5PdeNvFKtAVRNSwPDg1sIm6A ijZRcB6NfeMb/Yc543Yf450ewZTuebnJTQu55ywK5mpZExWiAZEr6z1cnbTfqG3Do82CWhak w16F5r0Vx25ckz0St8N5igdZOi57hS+dQLpxkVW2a6KqTQ+vbIjwEp4Kxj88PjI6CgZ9y6el mY4vmkDKXbGSDUjswUDQYuO2IXPT9GcE7H2grh2E5tvxH2DpL3lw5YFg5FPw6Ku8m1xYeh41 xZ+tLGoYs9Qg/PA2a7pDeWn8p227jJ9Q9MxAcA2KfXRA6qfMTc/gvYF3bsVYorV8XwNcoQWv WoYFz7gAhXvmkbad40c8fUr8cKP0aFnqlIvC0QBkhhRMocG/1PcahwIMepv+F2nNBOFeFmxr YvcYXzNWnG5S/QNJvc5jAvgNZMfYz7R7KJGLinjq7ED/5Ly/EaDFkDSwNAAxKkPabxfryhb3 6w7b509xa8EXRAi7reG04jFI+54PKMT5/FNrw9dhGnlJGU0KBQARAQABzR9BZGFtIEplbnNl biA8aGFuemVyQHJpc2V1cC5uZXQ+wsF3BBMBCgAhBQJSE27TAhsDBQsJCAcDBRUKCQgLBRYC AwEAAh4BAheAAAoJEGUApdpiqr3gNiAQAJ+lnXCQZZSsOYTxwXBlrVN5pepI8V+fd4ZZ3gn/ wt7dpP2Ak4FQNDch8XvahhS/guKvosZjSayqynPF2eXcR8sXNV2uMwKpqas139PM/956FqYv XdN/GOGVHxR9KanafzJwMJj5f6+BrhPp1GVRwQYyUhdlmnZJhHMt+lRLu1h27m7E/IAngYia RJ6a3m6XJQFkRMz8mjSNV0AZBlNNvpycEJoS8RbMoynR5LIjdWlJye25ufox1pjY/XVhw3hF 2HxRKAakO+q6Zc6v0MVmjDiIq+QxCqGdCoj0SACEmgOmI+SZl6rmenrSX0M/cktpNX9wyBYN CtxIdA6Zex+mOMmgo9g7T2n4xcqX83aIQKkuBJFfUj5yPBEWkjFvuYvtXbHogY3maFNYe+pm ZE6EZpBRZ8PCN17NLgxyh58yeJEx3oS1LDfqRz5SrFxfdN7bgExN3HKJJg7Jt32Oaml/8jSI BuxcEYimaP6MBkhrBHmd+TW3TUVGyQosqHGllQttXD92dm7wcaZnH0MNmroGG3+Czus2EgUS igFP4BwgNB8TaD17S6NQN83FVahAgjyhtadLtTzya/YSPrrvSHsKc88gasqNzvpsa+ggQZFX KALI/dozac61D6+UuTcUOvZCo5V4mIgeBMcw7PY6Ccycz9fHKghiPTuFdO1QI4KtujcXzsFN BFITbtMBEACnKEB3eI8FFIU4c9oDEkSPrZCwOOIjkaz9HfPOcLslFLVijqhPt4CCk5HM05KR yIYrYTyWytZfX+j2vW4TqJzntPBC+7oNjAIZ1l4UH6q8UxDya9+OLCx6awJNLo+5xmZ3pU7K vaTkvMguQfO7VWUK+f5nKYr/+lnZkxH0wuuLWomceEOk8Kt6g9+pQOC6w8FX4tYrtSFoSLHB bU7U1U4eJ3Hhp2b6PzHtJPtGORZ9i7CRRlZHONDNMw0EPcBkb5N0zgdLveVEUTfZfulR0TRN 2dBktBXArfoO3qZN4aKseLXU8XRQUG5xJNwf8vY+8fzKvgeh3BUgz2USYofRcTKEmkurvoA+ rKBMmGnW9s9pn8jlDLIB+V651YiJ+ip5HZL17pVI0zNDjKvqLw9EWGDaFh6WnIT+GTGKQBtu yzEZCKrPUkDxuqhZBRDw2ODdP37ccF9TippHW1e55oK3Wjgwon8DH7cJixzTXKIegTio459Y YE763IEXHjozhjZVYknaoysz4NRYJdYZKLjxsb4Xjc2BWLciumnEltaWGRl7MUblPH4jkMpD 4dOmYNpuhtUau8/UPpeHn8uaIHGlPGq4G+z5Wlaf6WVwLFOZRG8LuFMqhQ/8AeYjqDy3dB10 ruMDNpkiou/qDIIE2dHgvySMYntgPuEmCALHZMhIDvYCUwARAQABwsFfBBgBCgAJBQJSE27T AhsMAAoJEGUApdpiqr3gSxgP/jQJ34l85dNgrLqKOEOS7dfZzQRLjk0KkCIeCY/LmRDzyxD2 Gre5jtxFBuGNvcdaG5puC8rVpM0KW+MATqf8OjUhks71nz9aT+aKIga7oA/USl6QtErsdx7s g0BSz3kId481W3SUsvk7sXUcdRdri5SXZvfXhC9cL7UUKiPaL6zet/SMtIv0lSNGWHo44h3i IJVggPcwTW0p5eQGn8+dLTtJFBNfdpHv+pwgtBCBtMnYckfxkq+Mfk1vDhjVYQjtc03N6LbU zSALmELQeKdmjqhDZGniq651CveqMSaS6UKKbf2iIAMvBiyjl+jWGgUy9EMcNUJIxkAM8EWV Lv8MNu72oJhaAJpjSybiCeK2prK4ZTJF0h7ll81ftGVr1Cp/jRkw1Pof2bVQmlILHXkJLZJs unFsEcHpwnw52gZXc/zYjMGfyZy/ihP4Pyp+E7qlUKuLpkpEB43Ix5u8jmcOkMKhBnNo9OO1 p6Uwbl5GBIL/LXDKynYB98oXaxCzYkaxOrUWIu1ySJqRNnzAFMSwMqrR6fVumascjtAlBTMM sC/nN9VGG1VY0onGGJ80Vt+Fbhmj+zDpn0J3DfL2xD91+RxJaLCn8Iy/C4TikwsipXjnSmzA d6y+BJO5+lPxTTlg0UomOGv4ChmcVUEfXxk2GsvHZzsTA4qhLaZDuy9vR4eZ Message-ID: Date: Fri, 30 Nov 2018 15:19:10 -0500 MIME-Version: 1.0 In-Reply-To: <20181130175719.l7izv65wjwgkls35@alvherre.pgsql> Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 11/30/18 12:57 PM, Alvaro Herrera wrote: > This sounds like something you can easily do with range types in > Postgres. Probably not terribly portable to other DBMSs though. > https://www.postgresql.org/docs/11/rangetypes.html Nice hint. Thanks! PostgreSQL as the application platform is fine. Portability to other DBMS's isn't a requirement. The 'numrange' type with the 'overlaps' and 'intersection' operators seem to cover the fundamental computations in a very natural way. hanzer=# SELECT numrange(10, 30) && numrange(12, 32); ?column? ---------- t (1 row) hanzer=# SELECT numrange(10, 30) * numrange(12, 32); ?column? ---------- [12,30) (1 row) How might they be used in an SQL query to solve the problem? I'm an SQL noob. Seeing a few examples would be very informative. My impression is that it might require some serious kung-fu. :)