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 1qo7Ct-000D9M-9T for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 19:06:51 +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 1qo7Cr-002rmr-5k for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 19:06:50 +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 1qo7Cq-002rmN-Qh for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 19:06:49 +0000 Received: from mail-ed1-x52c.google.com ([2a00:1450:4864:20::52c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qo7Cp-0002kH-0J for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 19:06:49 +0000 Received: by mail-ed1-x52c.google.com with SMTP id 4fb4d7f45d1cf-533c4d20b33so490886a12.0 for ; Wed, 04 Oct 2023 12:06:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696446406; x=1697051206; 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=3izfUooFfjONlPFH8eSBVc/x8dUBipauwQH7Sd01PFM=; b=ftmBgaczS7ueyZJaVL8+7EFvZT+L7sgNnU0DLGEkGMh6A/nwZoygkITbKdD0ZrKz6b EkrEqywuO6TshpySXYDGbjAdidI8FkOZDFB+oyDdwITFJVf2HEx4grIGbkPBiy0ceECB m+v/kLqzMWpyZyAINO4rw0OnpRTtM1a2jFyHcU3WkVO8g7u2Bp0URVcs0NmFionCy0Ev NL7fOcH7yO6b3gyfz3J/AoW6ugW5cOUdq3U08qVsviAGW8QsynNVDwfBKHyHtvxgqoPM x79F2Fv1vp3dkuG/LC2fWQSq2l+WmKhF4V7QRJW3si+B1KXAYb4iLsXtH6eU0seLn0WZ RBYw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696446406; x=1697051206; 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=3izfUooFfjONlPFH8eSBVc/x8dUBipauwQH7Sd01PFM=; b=AI7U4WWL7IhShrKtipji2+hfPJoZidITepYEswgHoVlKaOU6MvjCOvpFkzhHGuoKaG oWp2xV6U8BVwdtP4p+yFJYqmTl3EYJDhF2oXSR4I5Si4kpjfibeAJOhEe9KMZbCY4iJu CdFOHfSph3H3t/dT8EaIJuiVKO0CdhQ8p5T2k3AjkFl1DqlCOOwcTwPpqsBotYLoqqBt IPJQO0ZMb1raR76o5OggSajtT+gN64eG6llq/yIsdIuz+VCMMTcSwS5dH/+t4KdnfOk2 icLKnC4ShHqJ2q6kC5aXA/Vmg4rVltqUtE/NejaJLE3tL0cILuSGoBlOK4gwEp6RKti3 AI8w== X-Gm-Message-State: AOJu0Ywz3Kk+N2nh5B6QFsSszbsItA6j7BUvopUXY8GekNEz3kK97kH2 3tqa2Sg6VZMWcnLNuoUHT+/Rk8kkbsk= X-Google-Smtp-Source: AGHT+IHIvfL9yoAL2MwnByNjaoYwLcvQue96kG1N3IG+eg/viCgVP3a2D0cY7B0X7r9pj+euwWRlsQ== X-Received: by 2002:a50:fb0c:0:b0:530:8759:a3ac with SMTP id d12-20020a50fb0c000000b005308759a3acmr598723edq.2.1696446405649; Wed, 04 Oct 2023 12:06:45 -0700 (PDT) Received: from smtpclient.apple (217-19-30-105.dsl.cambrium.nl. [217.19.30.105]) by smtp.gmail.com with ESMTPSA id i24-20020aa7c718000000b005362bcc089csm266987edq.67.2023.10.04.12.06.44 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 04 Oct 2023 12:06:45 -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 21:06:34 +0200 Cc: Marian Wendt , pgsql-general Content-Transfer-Encoding: quoted-printable Message-Id: 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 17:58, Lincoln Swaine-Moore = wrote: >=20 > > SELECT > > sub.gs AS ts_in_utc > > ,sub.gs AT TIME ZONE 'America/New_York' AS ts_in_local_tz > > ,date_bin('1 days', sub.gs AT TIME ZONE 'America/New_York',=20 > > '2023-01-01') > > FROM (SELECT generate_series('2023-11-03 00:00:00Z'::timestamptz,=20 > > '2023-11-07 00:00:00Z'::timestamptz, '5 minutes'::interval) AS gs) = AS sub > > WHERE > > sub.gs >=3D '2023-11-05 00:00:00 America/New_York'::timestamptz = AND=20 > > sub.gs < '2023-11-06 00:00:00 America/New_York'::timestamptz >=20 > I believe this query will be funky around DST borders, because `sub.gs = AT TIME ZONE 'America/New_York'` will be localized in a way that erases = the difference between hours with different offsets, which are genuinely = different. For instance, I ran this and there are two rows within it = that look like:=20 >=20 > ` 2023-11-05 05:00:00+00 | 2023-11-05 01:00:00 | 2023-11-05 00:00:00` > and=20 > ` 2023-11-05 06:00:00+00 | 2023-11-05 01:00:00 | 2023-11-05 00:00:00` >=20 > I think that the non-unique second column will pose an issue for the = date binning at a resolution finer than 1 day. 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). Filtering in the queries occurs on the UTC scale, with the local = timestamps calculated back to UTC, so that it doesn=E2=80=99t matter = whether the local time has 23, 24, 25 or 24.5 or 23.5 or whatever number = of hours on a date-range - it all maps back because UTC always has 24 = hours. Something that I also do is to create calendar tables and views for the = buckets, with 2 timestamps per bucket: the start of the bucket and the = start of the next bucket. That gives you a range to put actual = timestamps between (not BETWEEN between, because that=E2=80=99s = inclusive). You can store and index that, as opposed to generated = results using generate_series - basically I materialise those. 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). Regards, Alban Hertroys -- There is always an exception to always.