Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qo98z-000J0B-1M for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 21:10:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qo98w-0053fg-Sm for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 21:10:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qo98w-0053fL-Fj for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 21:10:55 +0000 Received: from mail-ej1-x633.google.com ([2a00:1450:4864:20::633]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qo98t-0003iI-1X for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 21:10:54 +0000 Received: by mail-ej1-x633.google.com with SMTP id a640c23a62f3a-9a58dbd5daeso48200366b.2 for ; Wed, 04 Oct 2023 14:10:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696453850; x=1697058650; darn=lists.postgresql.org; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=zcAdqjWU2N0jL6b7ha4InMNY0Tyf2RP8myipee03Pho=; b=EllouavlN3bcQOZmldZg90hTi7VTZHlpnEhSqt+cFIwfPkikKb859mR3Vx0ccHxkbW dYPhJoZ40bQA0NRck8zCNDs+iUlASX/cCa2qxGzzKNy4kTH2T+56BbGzn4hgExCOAAz4 S8HuyxbaARCLHkJbRNPDMHFE8PhSr8N/p1EMWXDqamRxqdrGL+7OS6TkOl9q8sHuLPbd NxVcHcVSSbN2YV1lrbNGPjN4NkIS9icpTG4wD3kS/tU8/oN1tdhiWJAXDK3mozgBQD2Q KSDARDNTmGP5wFHrNSK56m+b+hnJ6YlG5ckE1PzkXUEKpJnqvnd8gqwfLVFbhtZmj3A/ yAmw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696453850; x=1697058650; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=zcAdqjWU2N0jL6b7ha4InMNY0Tyf2RP8myipee03Pho=; b=uWXSGtqkkI9EeLSbdaFjdxuYUGrwBzbBXE8HuaaOPg/IZxG+4p9Suz+Is8l9zQIdSB jf9K4s4DSd30VycSrUEKLfy4RpaP5mFBXgfND034xASK8EODbm3BuQqHE/nyCV1fPCjq l+QT/RKrsYF1mEuOlG39xkR8jwiYFzpPIxxIPD8txyXayL5CYeCrQYwf1VNP1TiucjVj FA43atkjhvoWPg41TErV13oyHePveF77jwvd0ewI3PdjFRi8J/9pXKhv6K/NwV0ECaLq 0SydklO+KEg99vBW5itB7SAJ6FaIK4sdFUycatSaB1NLRV7vy7yBe4OvGOpof0Ecjt5f Rxqg== X-Gm-Message-State: AOJu0Yw8A36DolHnJMDep+kn4MfQ7smyTfvgUvR/38C6zFsMJmYq+03L f8aumt5g7yS2P8Aw8SocJn8= X-Google-Smtp-Source: AGHT+IE+PBJtfqqfy+gGFxVrZ0PM2dE+Bqa+Db7w4aYpze9vA1JTAPpHdCRbCHCKzUisT6hSZLCH1A== X-Received: by 2002:a17:906:328b:b0:9b7:58:52a8 with SMTP id 11-20020a170906328b00b009b7005852a8mr3280293ejw.19.1696453849495; Wed, 04 Oct 2023 14:10:49 -0700 (PDT) Received: from smtpclient.apple (217-19-30-105.dsl.cambrium.nl. [217.19.30.105]) by smtp.gmail.com with ESMTPSA id gv2-20020a170906f10200b009828e26e519sm3344493ejb.122.2023.10.04.14.10.48 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 04 Oct 2023 14:10:48 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.500.231\)) Subject: Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones From: Alban Hertroys In-Reply-To: Date: Wed, 4 Oct 2023 23:10:38 +0200 Cc: Marian Wendt , pgsql-general Content-Transfer-Encoding: quoted-printable Message-Id: <952AE7B1-5911-4389-A253-783CD011446D@gmail.com> References: <2582288.1696428710@sss.pgh.pa.us> <13eb1e62-9c00-4878-bb82-ef003eca884c@yahoo.com> To: Lincoln Swaine-Moore X-Mailer: Apple Mail (2.3731.500.231) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 4 Oct 2023, at 21:30, Lincoln Swaine-Moore = wrote: >=20 > > What I do in such cases is to add an extra column with the UTC = timestamp to serve as a linear scale to the local timestamps. That also = helps with ordering buckets in reports and such during DST changes = (especially the ones where an hour repeats). >=20 > > For hours and quarter hours I found it to be fairly convenient to = base a view on a join between a date calendar and an (quarter of an) = hour per UTC day table, but materialising that with some indexes may = perform better (at the cost of disk space). I do materialise that = currently, but our database server doesn=E2=80=99t have a lot of memory = so I=E2=80=99m often not hitting the cache and performance suffers a bit = (infrastructure is about to change for the better though). >=20 > That's an interesting idea, but I'm not sure I fully understand. = Assuming you're aggregating data: what do you group by? For instance, at = an hourly resolution, if you group by both the UTC timestamp and the = local one, you might end up, say, dividing an hour-long bucket in two = for time zones with half-hour-based offsets, no?=20 >=20 > Thanks for the detailed writeup! Definitely helpful to learn more = about what people are using in production to handle this sort of thing. Frankly, I haven=E2=80=99t had to deal with half-hour-based offsets = since I got this idea. I=E2=80=99m using it with whole-hour-offsets, = where it doesn=E2=80=99t affect bin boundaries. I suppose you could enrich your data in a similar fashion by adding a = (virtual) column with the (client) time zone offset, so you could group = by local timestamp + offset. That=E2=80=99s not going to match index = expressions though, I fear=E2=80=A6 For sorting, UTC timestamps would probably still be a useful addition, = but they=E2=80=99re simple to add by either converting back from the = local timestamps or by taking the min and max of the UTC-based column on = the above grouping. Both solutions require that offset, obviously. Now of course there are only 2 hours a year where this happens. Our data = scientists chose to ignore the problem for simplicity=E2=80=99s sake and = be slightly off with their numbers on those dates. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.