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 1gT5HA-0003RS-PU for pgsql-sql@arkaria.postgresql.org; Sat, 01 Dec 2018 13:25:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gT5G9-0007Rv-VJ for pgsql-sql@arkaria.postgresql.org; Sat, 01 Dec 2018 13:24:37 +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 1gT5G9-0007R7-Fb for pgsql-sql@lists.postgresql.org; Sat, 01 Dec 2018 13:24:37 +0000 Received: from mail-yb1-xb42.google.com ([2607:f8b0:4864:20::b42]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gT5G2-0005qr-Iq for pgsql-sql@lists.postgresql.org; Sat, 01 Dec 2018 13:24:36 +0000 Received: by mail-yb1-xb42.google.com with SMTP id u103-v6so3466883ybi.5 for ; Sat, 01 Dec 2018 05:24:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=joeconway.com; s=google; h=subject:to:references:from:openpgp:autocrypt:message-id:date :user-agent:mime-version:in-reply-to:content-language :content-transfer-encoding; bh=Ljt2SGqVfozdwn6F2glTFfb1vEpxRTq98F8MB+9P1rs=; b=kSxhlzuloI8dz273Jnfz7O/1ggYcOD7a5zArJhj3sPplBcbGF72HPAg7nUNRuL+Zcx zyxpsPI6AJUFDm7D/oisR2oF7ycAS/IxGRCfsTUNieCZOTqjXSwSsLiw9TolxuIM5oHN P50N0Xjx9xki4irZgjmCVy45IQBV4NhppAY5fbtB+tnG2nGCL5XB2HIqm7A7drCUqmO+ zncS8STMfOLk12zw1VCcsir3qxWe8yxAp/rUnHO9uwK7dRLLr6spuyIxoZVJRVfM9FUZ we3keaOi9gr+rGLof/SaFQro+XdWJTLKABoo6VkGa7Q9i69AhSjLkCh7c/mVFCbegOf+ wUEg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:openpgp:autocrypt :message-id:date:user-agent:mime-version:in-reply-to :content-language:content-transfer-encoding; bh=Ljt2SGqVfozdwn6F2glTFfb1vEpxRTq98F8MB+9P1rs=; b=OFfes32460y3jJC5viwajrp/H2BKxG93gwyZFR5ByL6hadHv5bXGCEomYSHr3ARRXQ t/6i5w+AAhRcsHyzC1pst3xK+eDqsTPrAfQOwvDFFWEkhevFJFTgG04dcE6mXOyOS6pX MURTlS98OF8k1lsBPzzfC9nP5Tlg1Itz4CIyPOHpCdCAAjY8iZMBFXijM0udp9fe22xv qMnEgvulAAUBsMyEAk8hsWVaQiRqRO9EJgo2ndsfd51DrOxdGFsfmPvr23XnPlURYfzI Sf0rD5gZrLUXVylSeCHhyeA2+IvsqWT63v8QsMCSUgEhmAUFRv17+h0KNT8h18Ls5fU3 FF3A== X-Gm-Message-State: AA+aEWanyhBSmyp4zgXtvbQDxzHF9URvYnEJst093vc+rU4MYYyxOCPv nVdEkS7G6d4q1i8LtPzl6GKO8hwFJVk= X-Google-Smtp-Source: AFSGD/ViTRa0g5dqQkKB7uiD5c6cNTDpPfLcdA+s5J2h64/uShTTGnTfowGuxfaWmHipxsuCpvgNTw== X-Received: by 2002:a25:718b:: with SMTP id m133-v6mr9035388ybc.79.1543670667782; Sat, 01 Dec 2018 05:24:27 -0800 (PST) Received: from [192.168.4.41] ([72.17.18.98]) by smtp.gmail.com with ESMTPSA id u127sm1262670ywa.109.2018.12.01.05.24.26 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Sat, 01 Dec 2018 05:24:26 -0800 (PST) Subject: Re: interval origami To: Adam Jensen , pgsql-sql@lists.postgresql.org References: <20181130175719.l7izv65wjwgkls35@alvherre.pgsql> <6d32b24f-2d3e-df79-81d2-3b1abb8cc4b4@riseup.net> From: Joe Conway Openpgp: preference=signencrypt Autocrypt: addr=mail@joeconway.com; keydata= xsFNBEpXMCsBEADDnXUQzjlyi/cX02Gtdy2CLcroE5CsC7DJKdOBDbfgn0kfiIYoV5JniG4l VyzZUodY8yUAagqLYolh0UkBzs9N+qkm7erde4ypw3jzVQ37BuzIvk3nMUbuDZDgxWqX+nVS sKc+BQ5BpzgCHg48leoRO2ohjvYnUhgH3j2rFZCzaj6qQ7mv+XoxOJmUlVQtG06Jwkk7Vu14 7U9nMMM6hyUKzVnmCphnlcMNo26UyVU70MwFfFJgcI0c5fpp8byN56eD6VJVnufO5WAuEhzE qcrSJR2FAlmM90GBY+6vP29twLDCHuSFvrnujNCx/BvCC/a3/gPvyAFp4JtMm9eXAmq3m/Kw 94nTJXVdcbQeQQDp3KIG7MmWS4lnGvPn8v0CjgNaLvZXFLo1FgmUVsyEq1Lww4iRLa6sbpXJ ESx15UEue1k1YZM9C+4F/o3aeKNsAienjw2EXFzcaxIg/C4P493VMi3Qa8ycVxR5iYhUbYdo DFIUQhbFNsYfrtW/qZAELT3FCYFpZYG01e9Hj+cBrXXgyDDkQ5Lq4mlvmkRvuxn61V6Au4HA 0sJiCox5pM1FvzT+aI8HY1BYaiB9Pl4fhpKgmhhlSuglk9v39S4jmlUIb45iLAUVpeNM6Qjm 69pf5da9sm4aGFa7YlDSKf/WcU7z9ITZxsilOi2n7YJiwG7kTQARAQABzR9Kb2UgQ29ud2F5 IDxtYWlsQGpvZWNvbndheS5jb20+wsF8BBMBAgAmAhsDBwsJCAcDAgEGFQgCCQoLBBYCAwEC HgECF4AFAlpoksUCGQEACgkQMyt+aLaZQ0p4XQ/9F4WSf4ZFuL3ae356ApVqnLxphHXeXHli ePZe7qosvXMQLN9Y2m5cQwqojsQh4pZujIi4Iwxw7ORg6HbMO8dMhyu8TlDavErcgO561hG7 /TCcCLf5SkadxBqYWAJH09W8S2iC3gQtS+qs4Y7sIvRi881EsPzG5Lrzu7Ei8ufqsSnbLUQk zAaKjK3IzAFvzA3VCg/8Aqhf8obhqL0j5I5DNOpAt2U4gkEzIrX1g5xSXpRLMHLEK7hPViY8 pFQyRvyZioE4hckNsnidCgiv4HefJUFRIVxDkHBeLhTzqTPloesFT/bskhckIS6F4suMx044 WTTW6UiARuWw3aaBTmxay2rjPCsGaJPawzY0O3HlNlZherCzuBpa+Qk1kMLkcxMlYjCrMgIR YyBpIK46ltPQaog4Ujh21w1Z8m/ufG5v/HTuLdXM9hGfTlW7aY6psCAV/MwfycDHLRMc+UY7 W2cp6DlWQ7GHLP+vzvBcl5Odi04EhcYnHMZjKvRFsxhE0LZcTejrtxPkDgR9G59irlIx1+py ziK2B0hs7Er1Yq7qQGQiQGLoglSNDsvsUeyMGyabasmUd5xrRO35SpGv+hyYGAZ3Pyk9Vtxl CO7BJkZiV9YSMHp6hOdmYZd4SZRChd9sKd6eSw1xIlBALz1ZHuQVMBuZvuVGFsS81Zqckb/2 D5rOwU0ESlcyJwEQAOkTBb9yDhJbMUgvhM11rZwT5tm4Y9TqtEHn0Zy3t9g7bdFFpMvav/KE Nd3oAtLFpMDf+H3AggFk4ftUwJwiVgJ88ilvCynJUGXiuYIaexY4DLgn4xpnuiEpYEFVdWnl w7dWVTc62exfqIz9bSWRzwfBCY9ruYGEb4RDPDSNSAVyI7sxHzef2asiYxIcxrTrw5VugWNl PZcV5/EJ6PUvATjBF2TBkXV7KOciQng2tsQGrGMkY5mduNqwpuh6zfPcVF8LeObe96wv5ZhP RpO79nef7hnK2lJogp3JIo558Jlbz9WHtQEMZR85+bUhtI825QyNAFz3Jrn7NMgvDikc2OrW o7YMgMC5hDSWVFqA6/EQCNnDWGABWgeYHZFpnPwsvUWIYdhSilUuj/Tuzvz9ZmucFNbQbauD Qw6VQ38ofGnoYDZFJsGncprB8dBi4tDrIQ+1RlIh6C2Z/eMipqJOT26+spluTjouvnKT0S5y OgyX0PjbsysgwQdCGNJLHOjhHbSpSmOLaduV3CQo/0+DHT/TBjYfIXjTWouY9TkGxG4eNrxU 0u2xAy5bMqOPmsFdjLTWlQUlF/fTMhB54XwI3FHWgnSnXZzStDTmTebLNdT/ftgliAzA81uM j49j0exv731/v+7udLA1bV8gnZ01zQCASDpWiRQR3fgwcugSUqgRABEBAAHCwV8EGAEIAAkF AkpXMicCGwwACgkQMyt+aLaZQ0pwAQ//bjcWnZg/jjRQ9gbZUGMqniItZYRglBMKIqt4Fia3 79JmHwTvavnFkJ8XMZ56UB0FIrgS+sUkRH6cPRQR+7Qi392LD021DXgSsz9CwFHjFyBGHwLE OTRcfYQbtJy0shHDJB4aQTOX3ERDH1PsvJNuevmQMzS0DWFav9+xMz9rKP4N+HffoBIZE0C1 xIE43nD4eLsbycte9sVIrmlNuUti3qUxJAQw8HwfJ6ZbBInHxquApR16uD1u99o6XlndFrDl Y22tRmHCM0bR81GfGNdcU3Uo+rG/R/k4qa7s9/dgKvMbyH3fHhp/ceKag80Xo8IFurRl0ZJP 3sHJ2QDHCVLat7jRZ+43hi1WlIhFbrgn6IyI0i7XR/W8JjrC5MsKq4TUwGH077sU/kcHYebV JZRbUUst2hAGHDFVBcG12qoKf+ltL9qXJc1y7BGeCoUW6QjOpljpq6ZL4FQUsM0RSRjs5egE 3szPcIf5SyPK6WDOApoAq6M7BBFMGDZwEylYMtr0YekA1u86UA9D2xwLHEbBBp/uiby1c9Jb PJ1Pn8zJP8WZNeRw4Q9TtqVK09+oLirMUSpIDd6KdZ1VgRxOK2re7tjDvkVuYsSrsiJ+1iJN Enp9iK0ok0DlJpSCe6KhkxpaTdeoWMXdKuJWec0NIqoAd54ZgBPnr+UPxTixgPq/p6Q= Message-ID: Date: Sat, 1 Dec 2018 08:24:25 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.2.1 MIME-Version: 1.0 In-Reply-To: <6d32b24f-2d3e-df79-81d2-3b1abb8cc4b4@riseup.net> Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 11/30/18 4:02 PM, Adam Jensen wrote: > On 11/30/18 3:19 PM, Adam Jensen wrote: >> The 'numrange' type with the 'overlaps' and 'intersection' operators >> seem to cover the fundamental computations in a very natural way. > > Actually, those operators might not be entirely sufficient. Given two > ranges like this: > > 10.0|39.0|interesting > 15.0|21.0|fail > > Something like the negative or inverse of the intersection is needed: > > 10.0|15.0|interesting > 21.0|39.0|interesting 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 HTH, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development