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 1qo7rT-000EsU-Fs for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 19:48:48 +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 1qo7rR-003Qpo-8E for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 19:48:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qo7rQ-003Qog-FQ for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 19:48:45 +0000 Received: from mail-sy4aus01on20703.outbound.protection.outlook.com ([2a01:111:f403:7005::703] helo=AUS01-SY4-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qo7rN-00023n-UW for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 19:48:44 +0000 ARC-Seal: i=2; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=pass; b=KaCTZvISBwaSsljYVyIWXhFV/2XgBoSzLJlb4GzFtruBkqrICcWDaoibB1tKWhXxVYxrY0MCc5Qy6uukfsjSlamLFL81QNhiT/fth8zMr7lLtw+247nEwWS01e5iC+3zqLJqr58mK8TIQm34U6xb/t7KmXlR8VGrRhK8L3jbtn3tTTd6CaKRMwO22KvO0Reb9S+v/b7BRiqLFwFSlfp/3r/axIvKmdK3bYz5QcbcZv2y4LDT2y+Bt8hWshGzL+ey3TBRdy6Q88WX/fTa8ONS4hCMhViay+6NbMjAkEX3vVIzYUzyM44ypTyDwC9peMgaJNhvMiRJ/GxP8wEYsdnoWw== ARC-Message-Signature: i=2; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=sdkylMGnoFut5fLk0obrhdHaE7wWe+GcCWwARbT4bKs=; b=IfBL1M2ndWOj2d9XR1X1Tw77YIYJ8RDPbqX5FN0qhq4Or/8ReVpu5P/eEMo1ms1kgvQQxCImDYTAU+k0m+f2wDXCRE9EUHZ0bgAR9pDznxkKZyME/dql+BKAjC1xYfkywYJcPoL/jTdH7z8TWhYiDHuWDzq+KDQbZ59hDEByQSn9iinRrZY8l19h2k36FwKgWvEUyWEKwyLDoP5PcfROp3kelWZsVR3gIKEXm75jE2cgDvaA7x/pfTYGh8wkJXJKqWRmlMm1NLt/OOCop3kw98Kg7QXqorAl+54tTGorxysndPhxDzpJjhukai/jZlejMyxLAmlXd0q7HDUc21vKhw== ARC-Authentication-Results: i=2; mx.microsoft.com 1; spf=pass (sender ip is 104.47.71.233) smtp.rcpttodomain=gmail.com smtp.mailfrom=niwa.co.nz; dmarc=pass (p=none sp=none pct=100) action=none header.from=niwa.co.nz; dkim=pass (signature was verified) header.d=niwa.onmicrosoft.com; arc=pass (0 oda=1 ltdi=1 spf=[1,1,smtp.mailfrom=niwa.co.nz] dkim=[1,1,header.d=niwa.co.nz] dmarc=[1,1,header.from=niwa.co.nz]) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=niwa.onmicrosoft.com; s=selector1-niwa-onmicrosoft-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=sdkylMGnoFut5fLk0obrhdHaE7wWe+GcCWwARbT4bKs=; b=AnM44zvaEddYAyL6kurdUAAKZeBViyh4D1qAkrxGYAsYBDBY8jdpLoCnA+5rWQ15zmeD9GEUr5+qirQ+Nt8cEajtXvyKzsO3K+UJnfNj7tRANSEdc3pMnIU1frHcN9GSl7nKFUbEGVr9w7Q78qh1O0vuEN1JKwHGj+benWaKX7Q= Received: from SYAPR01CA0047.ausprd01.prod.outlook.com (2603:10c6:1:1::35) by MEYPR01MB7378.ausprd01.prod.outlook.com (2603:10c6:220:15f::7) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.6838.34; Wed, 4 Oct 2023 19:48:34 +0000 Received: from SY4AUS01FT011.eop-AUS01.prod.protection.outlook.com (2603:10c6:1:1:cafe::65) by SYAPR01CA0047.outlook.office365.com (2603:10c6:1:1::35) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.6838.21 via Frontend Transport; Wed, 4 Oct 2023 19:48:34 +0000 X-MS-Exchange-Authentication-Results: spf=pass (sender IP is 104.47.71.233) smtp.mailfrom=niwa.co.nz; dkim=pass (signature was verified) header.d=niwa.onmicrosoft.com;dmarc=pass action=none header.from=niwa.co.nz; Received-SPF: Pass (protection.outlook.com: domain of niwa.co.nz designates 104.47.71.233 as permitted sender) receiver=protection.outlook.com; client-ip=104.47.71.233; helo=AUS01-ME3-obe.outbound.protection.outlook.com; pr=C Received: from wellmail.dmz.niwa.co.nz (202.36.29.34) by SY4AUS01FT011.mail.protection.outlook.com (10.114.156.123) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.6863.26 via Frontend Transport; Wed, 4 Oct 2023 19:48:34 +0000 Received: from AUS01-ME3-obe.outbound.protection.outlook.com (mail-me3aus01lp2233.outbound.protection.outlook.com [104.47.71.233]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "mail.protection.outlook.com", Issuer "DigiCert Cloud Services CA-1" (not verified)) by wellmail.dmz.niwa.co.nz (Postfix) with ESMTPS id AA90A601B2B; Thu, 5 Oct 2023 08:48:33 +1300 (NZDT) ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=VbzYho91oZZbpP91ZKdTrHIRErp21pr+y84Lyt9slMzqSDvi+q53CdeyrjpdHS3zYPZEWsPQXI0qUZMvF7nIz1J1V+rdXUYvko6wZ4pt8PcPEupFqkgZOzgoE197qVlCDGhOT2Muo2sFiaYxzvLCO1IVeHt9VBeH3grZc0Gdk0zQaLjEOfHLEO1rq0atHvOOytwG6REp3HjGDGVi4a8aFJ6DAoBB9zHCF6FQx5OK9m1gW5ym3cjMWMcDTHJX+q1Ez4zgw2Nu13jEvahvltnIvUktrk6qu0yrXvhpulUxnhJt8Fn9BoY+ggttEwxlBEcc4Z50hKggcuZui2vI5Fe87g== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=sdkylMGnoFut5fLk0obrhdHaE7wWe+GcCWwARbT4bKs=; b=UQWSV4w45uIa+/LeTT63Q7JfQCmd2ESWa5f/YeDegsQk+lVU9P6/Ce0BewMLUXGkWRNmdOwLp62DVyxzZsSkk7qVxCSjRIQ0/LhDRd39ds4kM9VvpvGmTeYszyd3neLDPUygf4YdE+LQK51RgtwkJtfb89nKSXxbHQxy5LAX0cxVhSLHUEJilk8xDRisThPQPLh7n/D3pUmWpZsc8UuRjOQhRRyH8k5baPU8I92g1QUNNyZlLCKW3ankNKkMbTcvhSnD5uY6X2oOlwdIjdq8dY6QsuBUkU2yRjmvHfOV4PEUZ5ossxJt9noQ4QvYVO93bKytU6CRNqDe0hpQ8ZgghA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=niwa.co.nz; dmarc=pass action=none header.from=niwa.co.nz; dkim=pass header.d=niwa.co.nz; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=niwa.onmicrosoft.com; s=selector1-niwa-onmicrosoft-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=sdkylMGnoFut5fLk0obrhdHaE7wWe+GcCWwARbT4bKs=; b=AnM44zvaEddYAyL6kurdUAAKZeBViyh4D1qAkrxGYAsYBDBY8jdpLoCnA+5rWQ15zmeD9GEUr5+qirQ+Nt8cEajtXvyKzsO3K+UJnfNj7tRANSEdc3pMnIU1frHcN9GSl7nKFUbEGVr9w7Q78qh1O0vuEN1JKwHGj+benWaKX7Q= Received: from SY4PR01MB7076.ausprd01.prod.outlook.com (2603:10c6:10:145::5) by SY4PR01MB6814.ausprd01.prod.outlook.com (2603:10c6:10:139::10) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.6838.34; Wed, 4 Oct 2023 19:48:31 +0000 Received: from SY4PR01MB7076.ausprd01.prod.outlook.com ([fe80::6a1c:8a77:f5e4:318b]) by SY4PR01MB7076.ausprd01.prod.outlook.com ([fe80::6a1c:8a77:f5e4:318b%3]) with mapi id 15.20.6838.033; Wed, 4 Oct 2023 19:48:31 +0000 From: Brent Wood To: Lincoln Swaine-Moore , Alban Hertroys CC: Marian Wendt , pgsql-general Subject: Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones Thread-Topic: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones Thread-Index: AQHZ9msktDfjG4uNrkeOHD+/ijoBC7A4+wEAgACxmACAAAwmgIAAEYmAgAA0qgCAAAbIgIAAAoG/ Date: Wed, 4 Oct 2023 19:48:31 +0000 Message-ID: References: <2582288.1696428710@sss.pgh.pa.us> <13eb1e62-9c00-4878-bb82-ef003eca884c@yahoo.com> In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: Authentication-Results-Original: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=niwa.co.nz; x-ms-traffictypediagnostic: SY4PR01MB7076:EE_|SY4PR01MB6814:EE_|SY4AUS01FT011:EE_|MEYPR01MB7378:EE_ X-MS-Office365-Filtering-Correlation-Id: 3386c340-4e9c-42c1-9d14-08dbc512e502 x-niwa-seemail: Out X-MS-Exchange-SenderADCheck: 1 X-MS-Exchange-AntiSpam-Relay: 0 X-Microsoft-Antispam-Untrusted: BCL:0; X-Microsoft-Antispam-Message-Info-Original: wOzpJxH7fIQnzNHZcaSYouOfqaFK4o9YfOrVWa319K9Ns2bcQl1tGEw1Nx38pEP9RNvm9+wS1sL9ph5eu75TywGbWhuNR4769M2+SrdH3+71/RCyR69rBSCJ/AlEVwxhnC4p1Gkg1niyvRoHUFhOmZ8fRVi5VAUa1zeP2QNVtsrDLVDx2Lu6oRlhmWnPXvLZrwqn+p4bhDfqpw+eJiLR4kN++2BB+NiEMwgaMSDBkXesHJqOLYPJuZvIKy3YQvXyKIkESObWujIwU1HNa7+KIiELd7miZy9kl+7AbHSgYWIHthhALyOict0xKHnQwI1sZU4cisdUySivtvEMof4guQdfHMNj8DuqLhIxgdrEtCKbENv8rUf8U1QpumUUHIBSoLxP2uczRMBZKKP4t+zLJj18JCyiCEwCVKbQ2aOiScJxYBxb9svVhUbHQ7GXzj0SymzuCqewQtUhIh5nzL3RP7gzSCw7376WpHcmse+NJeN/IjebxpMhXv0dKg1XhtPR50D62gFAxR+ep8uX2+9fk6h1NCk/0aZLN3Ixu+OzH2o3sycw0mbvGhqUyVZSa5w0j1SNaRmIprrwIpXz6v2nB+e6Lp2/bn36VQITUqwdJnN1FkDWQ7PuKM3gJmXAKM5l X-Forefront-Antispam-Report-Untrusted: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:SY4PR01MB7076.ausprd01.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230031)(346002)(39860400002)(376002)(366004)(136003)(396003)(230922051799003)(1690799017)(451199024)(64100799003)(1800799009)(186009)(33656002)(45080400002)(19627405001)(9686003)(6506007)(166002)(38070700005)(71200400001)(38100700002)(7696005)(53546011)(86362001)(478600001)(122000001)(66556008)(316002)(66476007)(54906003)(66446008)(64756008)(76116006)(66946007)(41300700001)(26005)(8936002)(4326008)(52536014)(8676002)(5660300002)(110136005)(83380400001)(55016003)(2906002);DIR:OUT;SFP:1102; Content-Type: multipart/alternative; boundary="_000_SY4PR01MB707634C6F5C0FFD83BA0E125A1CBASY4PR01MB7076ausp_" MIME-Version: 1.0 X-MS-Exchange-Transport-CrossTenantHeadersStamped: SY4PR01MB6814 X-EOPAttributedMessage: 0 X-MS-Exchange-SkipListedInternetSender: ip=[104.47.71.233];domain=AUS01-ME3-obe.outbound.protection.outlook.com X-MS-Exchange-ExternalOriginalInternetSender: ip=[104.47.71.233];domain=AUS01-ME3-obe.outbound.protection.outlook.com X-MS-Exchange-Transport-CrossTenantHeadersStripped: SY4AUS01FT011.eop-AUS01.prod.protection.outlook.com X-MS-PublicTrafficType: Email X-MS-Office365-Filtering-Correlation-Id-Prvs: 961c9b20-9af7-498d-bd39-08dbc512e366 X-Microsoft-Antispam: BCL:0; X-Microsoft-Antispam-Message-Info: =?Windows-1252?Q?4z2pC1wmuNLkZodrBeZmV+LzvCfuRcLMpMJ6OQ/z8BeW6+iX9biYutMm?= =?Windows-1252?Q?aF+rTovFuiJAh1UDGqywxZ8ot0dpcGYCsi2ZBVHI3sDo7DykkkHQDWp/?= =?Windows-1252?Q?bL6e1he+zL7OHCqMSzXEQNQrxf7gya0Lyww08sAGfbBmsON+DRFwHWAH?= =?Windows-1252?Q?PMp0aJLZObsQ6TLXm8nDr8NjsxhE1smgre4cHRfrQWmJ3CYnRLNF90lV?= =?Windows-1252?Q?o9teUqzZshl93wFbfUKHPjvjqwwRGMvzZhHbJppOAYgWtxO5BJV+1H3D?= =?Windows-1252?Q?OeZjri/+aqH1wyi5x5NZQ4Qz46HQDiYiHyTCFrsgrwQrbHawpYFGfzkG?= =?Windows-1252?Q?Sr2o+BPYrg3fdswGjgtR8fs/NLWM44pV+EgYoze5WIRt8tc94LrmgHgs?= =?Windows-1252?Q?fseSFqGjRFU+K9NRJVlxTmbJTEyDPkrjiDH+VRtKl0dqA8yOaRZ8x/c7?= =?Windows-1252?Q?WLjU48sYqrs4nGLrZETn9HNcN7FZjv6WDu28hvx9rVw92/PNOEdNotjD?= =?Windows-1252?Q?6BxV5tQT8/qtoKN/N+u4nCt3Khb+pdXvneof+KBE0vq5IoUv9SqY+q2e?= =?Windows-1252?Q?ynbc8t4E1+bcpcJFbogIr0IWAPJIo1yuJTZwjAHF+c23I8bh6dFGejUq?= =?Windows-1252?Q?xXRqzg6Ip3qsLhPvvIlvEcWIJoFh8GKvShN+FfGzfYsm4yNxswQaZFpo?= =?Windows-1252?Q?7sTFTNf3wL1briggAWTWLWU+iaJR7Cgax4n5eMVmHMFPauqbMOJ0yiMp?= =?Windows-1252?Q?VjSVSMmv/W79PVW9RFOzsJ8Lf67U6BSZ4jqDewuSzbStywENSYbEFYNs?= =?Windows-1252?Q?n0Mpx2My9QUubLZbajQIToGWcS6irx/vWGpifNiIk10+ClRkEJeEsF2c?= =?Windows-1252?Q?ZO5IOdsmHTlQKYFWAhWHulZzH9BQcVzO9ylwNKdxqPcVeXFSrdgY6URD?= =?Windows-1252?Q?Y0GF6HTYg2OgsnR4IVin4mu569p56hFso/gQ21ukQsTLh4Po6YqNcHvD?= =?Windows-1252?Q?pZmMFxfaED2TzaFIV0sbDXbC2gDxRMwHN1qScHz9jhTGvFaiKLo=3D?= X-Forefront-Antispam-Report: CIP:202.36.29.34;CTRY:AU;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AUS01-ME3-obe.outbound.protection.outlook.com;PTR:mail-me3aus01lp2233.outbound.protection.outlook.com;CAT:NONE;SFS:(13230031)(4636009)(396003)(39860400002)(136003)(376002)(346002)(230922051799003)(82310400011)(186009)(1800799009)(1690799017)(7200799017)(64100799003)(451199024)(36840700001)(46966006)(40470700004)(40480700001)(26005)(53546011)(336012)(45080400002)(6506007)(47076005)(83380400001)(7696005)(478600001)(36860700001)(9686003)(4326008)(70586007)(5660300002)(2906002)(8676002)(70206006)(110136005)(52536014)(316002)(8936002)(54906003)(41300700001)(40460700003)(7636003)(55016003)(32850700003)(166002)(82740400003)(156005)(86362001)(33656002)(19627405001)(8400799017);DIR:OUT;SFP:1102; X-OriginatorOrg: niwa.co.nz X-MS-Exchange-CrossTenant-OriginalArrivalTime: 04 Oct 2023 19:48:34.4200 (UTC) X-MS-Exchange-CrossTenant-Network-Message-Id: 3386c340-4e9c-42c1-9d14-08dbc512e502 X-MS-Exchange-CrossTenant-Id: 41caed73-6a0c-468a-ba49-9ff6aafd1c77 X-MS-Exchange-CrossTenant-OriginalAttributedTenantConnectingIp: TenantId=41caed73-6a0c-468a-ba49-9ff6aafd1c77;Ip=[202.36.29.34];Helo=[wellmail.dmz.niwa.co.nz] X-MS-Exchange-CrossTenant-AuthSource: SY4AUS01FT011.eop-AUS01.prod.protection.outlook.com X-MS-Exchange-CrossTenant-AuthAs: Anonymous X-MS-Exchange-CrossTenant-FromEntityHeader: HybridOnPrem X-MS-Exchange-Transport-CrossTenantHeadersStamped: MEYPR01MB7378 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SY4PR01MB707634C6F5C0FFD83BA0E125A1CBASY4PR01MB7076ausp_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Slightly off topic, but has anyone tried TimescaleDB for timeseries databas= es? The issues discussed here are still there as they apply to the underlying P= ostgres ORDBMS. We solve the problem (around 4 billion records of instrument sensor reading= s) by using UTC for the "native" timestamp, and working in that. Even thoug= h we are =BD way around the world. The local times can easily be determined= & applied if desired, but by standardising on the reference time zone at t= he start, things have "just worked", for around 15 years now. Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 ________________________________ From: Lincoln Swaine-Moore Sent: Thursday, October 5, 2023 08:30 To: Alban Hertroys Cc: Marian Wendt ; pgsql-general Subject: Re: Strategies for converting UTC data to local windows for arbitr= ary resolutions and timezones > 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 or= dering buckets in reports and such during DST changes (especially the ones = where an hour repeats). > 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=92t have a lot of memory so I=92m often not hitting the cache = and performance suffers a bit (infrastructure is about to change for the be= tter though). That's an interesting idea, but I'm not sure I fully understand. Assuming y= ou're aggregating data: what do you group by? For instance, at an hourly re= solution, if you group by both the UTC timestamp and the local one, you mig= ht end up, say, dividing an hour-long bucket in two for time zones with hal= f-hour-based offsets, no? Thanks for the detailed writeup! Definitely helpful to learn more about wha= t people are using in production to handle this sort of thing. -- Lincoln Swaine-Moore [https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png] Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 National Institute of Water & Atmospheric Research Ltd (NIWA) 301 Evans Bay Parade Hataitai Wellington New Zealand Connect with NIWA: niwa.co.nz Facebook LinkedIn Twitte= r Instagram YouTube To ensure compliance with legal requirements and to maintain cyber security= standards, NIWA's IT systems are subject to ongoing monitoring, activity l= ogging and auditing. This monitoring and auditing service may be provided b= y third parties. Such third parties can access information transmitted to, = processed by and stored on NIWA's IT systems. Note: This email is intended solely for the use of the addressee and may co= ntain information that is confidential or subject to legal professional pri= vilege. If you receive this email in error please immediately notify the se= nder and delete the email. --_000_SY4PR01MB707634C6F5C0FFD83BA0E125A1CBASY4PR01MB7076ausp_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
Slightly off topic, but has anyone tried TimescaleDB for timeseries databas= es?

The issues discussed here are still there as they apply to the underlying P= ostgres ORDBMS.

We solve the problem (around 4 billion records of instrument sensor reading= s) by using UTC for the "native" timestamp, and working in that. = Even though we are =BD way around the world. T= he local times can easily be determined & applied if desired, but by standardising on the reference time zone at the start, things have = "just worked", for around 15 years now. 


Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529

From: Lincoln Swaine-Moore = <lswainemoore@gmail.com>
Sent: Thursday, October 5, 2023 08:30
To: Alban Hertroys <haramrae@gmail.com>
Cc: Marian Wendt <marian.wendt@yahoo.com>; pgsql-general <p= gsql-general@lists.postgresql.org>
Subject: Re: Strategies for converting UTC data to local windows for= arbitrary resolutions and timezones
 
> What I do in such cases is to add an extra column wit= h the UTC timestamp to serve as a linear scale to the local timestamps. Tha= t also helps with ordering buckets in reports and such during DST changes (= especially the ones where an hour repeats).

> For hours and quarter hours I found it to be fairly c= onvenient 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=92t= have a lot of memory so I=92m often not hitting the cache and performance = suffers a bit (infrastructure is about to change for the better though).

That's an interesting idea, but I'm not sure I fully understand. Assum= ing you're aggregating data: what do you group by? For instance, at an hour= ly resolution, if you group by both the UTC timestamp and the local one, yo= u might end up, say, dividing an hour-long bucket in two for time zones with half-hour-based offsets, no?&n= bsp;

Thanks for the detailed writeup! Definitely helpful to learn more abou= t what people are using in production to handle this sort of thing.

--
Lincoln Swaine-Moore
Brent Wood
Principal Technician - GIS and Spatial Data Managem= ent
Programme Leader - Environmental Information Delive= ry
+64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz= Facebook LinkedIn= Twitter Instagram YouTube
To ensure compliance with legal requirement= s and to maintain cyber security standards, NIWA's IT systems are subject t= o ongoing monitoring, activity logging and auditing. This monitoring and au= diting service may be provided by third parties. Such third parties can access information transmitted to, process= ed by and stored on NIWA's IT systems.
Note: This email is intended solely for the use of the addressee and may co= ntain information that is confidential or subject to legal professional pri= vilege. If you receive this email in error please immediately notify the se= nder and delete the email.
--_000_SY4PR01MB707634C6F5C0FFD83BA0E125A1CBASY4PR01MB7076ausp_--