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 1gTC1b-0001U0-0c for pgsql-sql@arkaria.postgresql.org; Sat, 01 Dec 2018 20:38:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gTC1Y-0005Lh-25 for pgsql-sql@arkaria.postgresql.org; Sat, 01 Dec 2018 20:38:00 +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 1gTC1X-0005JB-EB for pgsql-sql@lists.postgresql.org; Sat, 01 Dec 2018 20:37:59 +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 1gTC1P-0007EP-0B for pgsql-sql@lists.postgresql.org; Sat, 01 Dec 2018 20:37:58 +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 DBD211A0444 for ; Sat, 1 Dec 2018 12:37:47 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=riseup.net; s=squak; t=1543696667; bh=ME+OHLeH7ds0ojzP1K7G7ELpu1bFvEgoYA/VyNCBJb4=; h=Subject:To:References:From:Date:In-Reply-To:From; b=H+MCquQRl/wTcodG3jR7AOWTsJnEJ59YN4tS3SrhemkBIV3ZhHQxZyFmG3L86zmna xaitb4cTmOIW27q0NqU3ZiJ9PZD6UVI3xUNBk+K+16aL5k+aEM4EW6JJq+96AzCEBl Zpb2tc2ZP7j5LaTYHD/fi30iFR9SVhlXKbBiOb5U= X-Riseup-User-ID: DB4604C160DEE809BB427910A715672B0A3040FF9B63F45C63504B46F90278E5 Received: from [127.0.0.1] (localhost [127.0.0.1]) by cotinga.riseup.net with ESMTPSA id 8F89E8EA5E for ; Sat, 1 Dec 2018 12:37:47 -0800 (PST) Subject: Re: interval origami To: pgsql-sql@lists.postgresql.org References: <20181130175719.l7izv65wjwgkls35@alvherre.pgsql> <6d32b24f-2d3e-df79-81d2-3b1abb8cc4b4@riseup.net> 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: <60f4dcd0-bd8c-f7ba-cf77-e1640f3f5dc0@riseup.net> Date: Sat, 1 Dec 2018 15:37:46 -0500 MIME-Version: 1.0 In-Reply-To: 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 12/1/18 8:24 AM, Joe Conway wrote: > Perhaps overkill, but if you represent your timeline as actual line > segments, perhaps PostGIS would be useful. E.g.: > > https://postgis.net/docs/manual-2.5/ST_Difference.html That's an interesting notion. Thanks, Joe! I think I learned enough about plpgsql programming last night to write the three basic functions that each operate on two time intervals: 1. determine if two intervals overlap 2. merge two overlapping intervals into one interval 3. given two overlapping intervals, produce the difference interval or interval set My thinking here is that since these functions seem to be computationally simple, it might be more convenient for exploration, development, distribution and maintenance if there is some uniformity in their style and control over their implementation and behavior. Currently, I am thinking about how these functions might be used to solve the problem. Since I am not familiar with the capabilities of SQL, my tendency is to think in terms of a function that iterates over the data set multiple times and eventually converges to produce the solution set. I am concerned that this might be a very goofy way to solve the problem in a relational database.