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 1taHl6-000Qgr-UD for pgsql-general@arkaria.postgresql.org; Tue, 21 Jan 2025 17:09:50 +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 1taHl5-001qzC-WD for pgsql-general@arkaria.postgresql.org; Tue, 21 Jan 2025 17:09:48 +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 1tZbcW-009Ueu-9u for pgsql-general@lists.postgresql.org; Sun, 19 Jan 2025 20:10:09 +0000 Received: from mail-sy4aus01on20705.outbound.protection.outlook.com ([2a01:111:f403:201e::705] helo=AUS01-SY4-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tZbcS-000RBt-2b for pgsql-general@lists.postgresql.org; Sun, 19 Jan 2025 20:10:06 +0000 ARC-Seal: i=2; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=pass; b=uX35TK/MTeRO6tSUqiqGQfTKSfxohMQlCclvJLoc+5ITuN2vp/vVGNX+cPYCClBAwv5WqQ02EyOX9sOIF8ZmcAK/xLpo3dyrO9gprJ+7I0ACjas3inDLJ/Ztmhx08Zg28y/LLjSuBkNu9KbvG5ZjzxXbF4W/otISCOH8tL0ckix6URsxrFHYMDu74mNmZjuV14a5gZ/Z6hxOz72YeTteaXjJits+hUEvHnETILzqUCEgwCYx4LtpoUCE2C500nwb81bARFgxl/quLzupr4FO+aUb3FHwl1aK+4empA5MEjKKH7NsZzObz4yRNic34Yf4GhexoO0yQnSleTFwMfqgDw== ARC-Message-Signature: i=2; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; 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=4vH/SONgbdj67RDBFgjza9JPN9Xi2d4/IsDAss7iJd4=; b=nGpQxZ3/YuyP9tvKcadlkyC3lZhAx9f979Uw/jyev3RGhWs0PCvrz2baX1foPaDdPA3JQCmHdL+TjiUPOL+6MGqMSwcliEY2iWkxhQ1iwV1xtD7RcyZudizWHugLEQF5TagcoJkFHqTRyA7UzApCYSLy3N4/cBCcS18Tgz+HK0qYT3e6Zm02fre5iC1r9r9/1kezd90k0J6O7XT9toH8JKoLSp3p9oGztxZqLzPbN7meyzAs0pJyPi9J1W+NggNl5vv5X7Ry7XzMktp/LbnN5EmKldAaJ88L6J9crdSKMW9WcS2DhRTdnSySwkRLvl5W+xzLaddmQ+IYQN/PO77Gxw== ARC-Authentication-Results: i=2; mx.microsoft.com 1; spf=pass (sender ip is 104.47.71.168) smtp.rcpttodomain=lists.postgresql.org 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.co.nz; 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.co.nz; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=4vH/SONgbdj67RDBFgjza9JPN9Xi2d4/IsDAss7iJd4=; b=ndSo+qPvlzd82zwWQllHS2PTt5Fs+BKHr7fJSXNFmP1+JstXKmAOjBJ8tKk59tVar8F8ya3o2gTyrv6Vgg802Is38WF/GFGghTFJ6ELjx4Cx/qgXA7EEwILpVknit5U1iB2R3pIH7MoOxud1CGGCYBx7Oz57vh8+/406vrt3+8g= Received: from SYCPR01CA0046.ausprd01.prod.outlook.com (2603:10c6:10:e::34) by SY8P300MB0064.AUSP300.PROD.OUTLOOK.COM (2603:10c6:10:262::12) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8356.21; Sun, 19 Jan 2025 20:09:56 +0000 Received: from SY1PEPF000066C2.ausprd01.prod.outlook.com (2603:10c6:10:e:cafe::10) by SYCPR01CA0046.outlook.office365.com (2603:10c6:10:e::34) with Microsoft SMTP Server (version=TLS1_3, cipher=TLS_AES_256_GCM_SHA384) id 15.20.8356.21 via Frontend Transport; Sun, 19 Jan 2025 20:09:56 +0000 X-MS-Exchange-Authentication-Results: spf=pass (sender IP is 104.47.71.168) smtp.mailfrom=niwa.co.nz; dkim=pass (signature was verified) header.d=niwa.co.nz;dmarc=pass action=none header.from=niwa.co.nz; Received-SPF: Pass (protection.outlook.com: domain of niwa.co.nz designates 104.47.71.168 as permitted sender) receiver=protection.outlook.com; client-ip=104.47.71.168; helo=AUS01-SY4-obe.outbound.protection.outlook.com; pr=C Received: from wellmail.dmz.niwa.co.nz (202.36.29.34) by SY1PEPF000066C2.mail.protection.outlook.com (10.167.241.52) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8377.8 via Frontend Transport; Sun, 19 Jan 2025 20:09:55 +0000 Received: from AUS01-SY4-obe.outbound.protection.outlook.com (mail-sy4aus01lp2168.outbound.protection.outlook.com [104.47.71.168]) (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 93FD2609D61 for ; Mon, 20 Jan 2025 09:09:54 +1300 (NZDT) ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=xLj1npMimJl4AMQ5/QyE2Y+B8I9DLUjv3pjUwqGhB4MFn+ZVBvrRbz/AQ/nKeQE8+YBWgcvE7SpkEqvEzTi9uCqGU8qnk+mgxUpQwxrjq+Mh5d1rAG3POjFh6s0AGqTtk4VoUfIYyHwG2lgCbvijLRfuVrh2MuVcit/UWbO/qLRjrHwqkl8+E93FuU9p5/7QQ+ID9cCOVaX0BZv299kmVdO3e2+UZ6kMcc1jsl4c0vEwubxhkP1X/WJVgcNeTnmfOKB6NLFfkGW8i3dtrS2CPltlb0piN3tOMA8UFfSAcNe18zaGaXvNOOnHBqPwjmWUxhldK+LdCZCPFDB8mnw5qA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; 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=4vH/SONgbdj67RDBFgjza9JPN9Xi2d4/IsDAss7iJd4=; b=mV/dwuXPNIoRxJHVLswT5oomQZ5lcM3xx/ECfVf6s2JOcOwKDcDQIPGLmd6MbXXk2WvHSmLZbs5UwU8hJbVUwx+GxgUma92BPskY4mEz3Y45/90/e05v4SKZTlAPRIq5tiJ9Ocpo3yqBWCbTPWZhU3VYk1OVx8EiHp4sHUmD7hkR7XA6pR75EejuSAQLDKJrxnB9P6UM/on7xAJ7RoMLbRHfT42llC1a+s56kPWud9NQtjFn2uKtNZBCCA1eUSd4FS/ehbYHPHYsOO8W9Gpm1+MJI80EdttdzNUPu7UTQBBBkf4usmSbydFSExuvsvbLrlf4f/cMkDq1TPAYqjOX2w== 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.co.nz; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=4vH/SONgbdj67RDBFgjza9JPN9Xi2d4/IsDAss7iJd4=; b=ndSo+qPvlzd82zwWQllHS2PTt5Fs+BKHr7fJSXNFmP1+JstXKmAOjBJ8tKk59tVar8F8ya3o2gTyrv6Vgg802Is38WF/GFGghTFJ6ELjx4Cx/qgXA7EEwILpVknit5U1iB2R3pIH7MoOxud1CGGCYBx7Oz57vh8+/406vrt3+8g= Received: from SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM (2603:10c6:10:28c::10) by SY7P300MB0276.AUSP300.PROD.OUTLOOK.COM (2603:10c6:10:236::7) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8356.20; Sun, 19 Jan 2025 20:09:48 +0000 Received: from SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM ([fe80::da5c:b8f6:3849:775c]) by SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM ([fe80::da5c:b8f6:3849:775c%2]) with mapi id 15.20.8356.020; Sun, 19 Jan 2025 20:09:48 +0000 From: Brent Wood To: "pgsql-general@lists.postgresql.org" Subject: Re: concatenating hstores in a group by? Thread-Topic: concatenating hstores in a group by? Thread-Index: AQHbaSBvglWD5lV+Pkqu6Mk3BgaSQLMeO1cAgABLsTI= Date: Sun, 19 Jan 2025 20:09:48 +0000 Message-ID: References: <2268303.1737134384@sss.pgh.pa.us> 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: SY7P300MB0761:EE_|SY7P300MB0276:EE_|SY1PEPF000066C2:EE_|SY8P300MB0064:EE_ X-MS-Office365-Filtering-Correlation-Id: c9425e16-5add-4168-d02b-08dd38c53de4 x-niwa-seemail: Out X-MS-Exchange-SenderADCheck: 1 X-MS-Exchange-AntiSpam-Relay: 0 X-Microsoft-Antispam-Untrusted: BCL:0;ARA:13230040|366016|69100299015|1800799024|376014|38070700018|7053199007|8096899003; X-Microsoft-Antispam-Message-Info-Original: =?us-ascii?Q?Eq/sfTaC8jyfnleoswrSrNL0fIAwBO/OORad936jEEwSaxSd6x3dF3jvu1RB?= =?us-ascii?Q?V7nKT2P5//OIghJZzXrC5fsh5GDIZnJ2/PYg15KxI/VLtCuBuG3x4oR84JQi?= =?us-ascii?Q?iS7/lhnexokz2OD4FMKtcWEe/9hLNe7P4aijjqPr1LmKPUHFY2bjNONLxCP5?= =?us-ascii?Q?RP9DSP/Rvw3dAGJBQPM7b7HZx4Bgz0C/4nVvZa2azjDD4Gb/Uy80r0gBxHi5?= =?us-ascii?Q?Q+Jw8gA4N3DtNQtppdv8jd3AbpRezAZBKmYXbY/X149woR8A3Zj+59krAJ2x?= =?us-ascii?Q?Dz+JmFTDdElAun6LwReQyl6ve1SbHOes4wK+ZGCQwMeuVlY1i0B0WEioqzIS?= =?us-ascii?Q?DZu6i03DGyEXAMe74WFxHVAARqPapmixt+rM6/v2zmjzPdgCz3ByDKYQbIHp?= =?us-ascii?Q?AZ2ehfXI/ZoA+mgXdlbRKHXP+BYdnDcXTQCwnx2OEcD+lrG+2zzA3PU6qWrT?= =?us-ascii?Q?od3bRVYqIYnVlVS9kqAoFGQ9IAeJFSfJZj6LfNYurbi9orxPq7tVtwRAX8q0?= =?us-ascii?Q?uOn2myJfuMKugOesvYOh+XVfRMtq78CJb6YNXIQliAIS1TLI9F11E0XvkyE8?= =?us-ascii?Q?Rp3DNA0TrrvkUjwAdJa+IibzLRmdEpKkGosJyhEO+POgQtG3dBG5y8IUupAY?= =?us-ascii?Q?GNzSc2PvCfUoNNYvzt/dTv0MrOjLFbJEAfFJDickToy2vumAxrqqSdbzq9NF?= =?us-ascii?Q?2q9lUTTNYtB1b+r8HS8Cy/Q/Rzkib4Erx66+tSpgEcAaV2pwYxOr6oguwI+M?= =?us-ascii?Q?xortKvZOk3/fG9e04du5t0bkBOx1M9CVApTuW5WNLerstg3b3ljZo+A/LB95?= =?us-ascii?Q?LOVuUGL4YsYvlwMBfWLVDDk8Pd1bNHSl00DH6mjq5HccjYC7TbV3g+u5JjN7?= =?us-ascii?Q?gyKz337HqDmZA/xn3yqDlx1HJ0HBGO1NlZsAtup+61QWW3p9h3+hKhU2GYN2?= =?us-ascii?Q?mlAxebDL1Jgdh49wXrzcOu+ezQrCcRlM+24TgQIqCBL+uG7ywiUnfZgUtw7m?= =?us-ascii?Q?aE94bT/PvBCcKZpSi+Cw/Jsbc8rbdsEtYHQYUYnxzZlKwhQH3vE2ff+5n9CS?= =?us-ascii?Q?MABKF8dxjhn4wj0ZffdPErCQRXWc4wGL69Inis5yt2MhvOXrlSHcO/Tw1elo?= =?us-ascii?Q?E/4QE3YczW/BLvvtPiJkyd/pEmcNqgHJe8y7+nH/xNnJEnYb9vjlMil68F72?= =?us-ascii?Q?zF1kFnUTnK7s1Pm16D6Iy4BzFIu3btFLGBz5v1sdjj5r3Le5CyfYwl1DnZZ7?= =?us-ascii?Q?SElhVie9o5/1gXEZ971gowFz/jvIdKx18nFMUeFuOBgONTBOKbhVOk3Bzt2J?= =?us-ascii?Q?f2rJ/F7tlRtr5noNreNmSO+Ab+nxIc2CxV7TgNGtdm0plSGFpomHUExGnJdc?= =?us-ascii?Q?no6EsFVEOFN+uqCxCUrvETaBOvIZ5S/uhL8WjOvgEQ2kf0ReeQFTBQvipXGy?= =?us-ascii?Q?zTLhnQ1mM6Bj84W4ALrU0+ZaqLSkFxKX?= X-Forefront-Antispam-Report-Untrusted: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(366016)(69100299015)(1800799024)(376014)(38070700018)(7053199007)(8096899003);DIR:OUT;SFP:1102; Content-Type: multipart/alternative; boundary="_000_SY7P300MB0761B08085ED0691EBA1B78DA1E42SY7P300MB0761AUSP_" MIME-Version: 1.0 X-MS-Exchange-Transport-CrossTenantHeadersStamped: SY7P300MB0276 X-EOPAttributedMessage: 0 X-MS-Exchange-SkipListedInternetSender: ip=[104.47.71.168];domain=AUS01-SY4-obe.outbound.protection.outlook.com X-MS-Exchange-ExternalOriginalInternetSender: ip=[104.47.71.168];domain=AUS01-SY4-obe.outbound.protection.outlook.com X-MS-Exchange-Transport-CrossTenantHeadersStripped: SY1PEPF000066C2.ausprd01.prod.outlook.com X-MS-PublicTrafficType: Email X-MS-Office365-Filtering-Correlation-Id-Prvs: fde804de-80bc-4cdd-dd62-08dd38c539de X-Microsoft-Antispam: BCL:0;ARA:13230040|14060799003|156008|376014|69100299015|35042699022|36860700013|1032899013|1800799024|82310400026|13003099007|8096899003|4076899003|2066899003|7053199007; X-Microsoft-Antispam-Message-Info: t2YLBH9JWLhITUqHhsIiseTYnzy5hTxaaw9zKIPqZyoVN7E0WM5Lh2JUozyWfMLWaYSo9PaXnCtTnLm4fdm5VV7nS3QAWNQkGR4BSi9ZX4Tl8K5IM/FE3/li0sXsndvvASqqs0S1hsIoBd3Mz6Wk8JkW14F60kTPSUSDqV9VvXz/T1JOgWVZhhRDiNxbRSYvEi5VGl5LhIF0LeQK0oirKMH0mHTKhg9TRdIJG7VoGyiNQIvT7N/6/9F+W+TzmFDv/aVEFYUpkJHBpWyFK2jSUviga54H6NW0h9uHmBCW1AY0QjTZkzB8h3PzXlj6IQlZNw+fvFgTp2cnBZuJrKbLX0OKLOJUTHxvgjkldFMTgiDQ6AY6MqoZTvOL8/dadB1kcry7F8dCa+AmhxGx8m5z1caP8PRXxLOU+MkZu+u1YMO2W58blnHG+Z/sCJRZbW2hvLWH2tiyVjZns6vHIvmp16aYARd/mT+Qbnm3s1vDzMva91zwqbz/j7507NwMBA63ZXOs7IBfbtCuG51b0zR7ilph5RSGN0f0wJ990E+93VOc8rg63rHZugKn0d7SACws+diQ9HI42BZQwVWnXxn0YsmGu5yTZqXoR5VN3qR8HAvY3siNLHa0/togTK4aGT3fuk1dkOYqN18GJSMQ/S+VQfAb5s7o2tl3o6UhkISSR5poxdycyssoAmhH/f3fe8vxJu3hfhQDycxFB2OIeWhC+5PadsiW+Cd3JqqkJBev706QiF/c98kKRAUWQwa7ZsWCbCgxjeXzqvoYGLRdwnhd4U0GdfE+CRfoeHNXt4B5DMA= X-Forefront-Antispam-Report: CIP:202.36.29.34;CTRY:AU;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AUS01-SY4-obe.outbound.protection.outlook.com;PTR:mail-sy4aus01lp2168.outbound.protection.outlook.com;CAT:NONE;SFS:(13230040)(14060799003)(156008)(376014)(69100299015)(35042699022)(36860700013)(1032899013)(1800799024)(82310400026)(13003099007)(8096899003)(4076899003)(2066899003)(7053199007);DIR:OUT;SFP:1102; X-OriginatorOrg: niwa.co.nz X-MS-Exchange-CrossTenant-OriginalArrivalTime: 19 Jan 2025 20:09:55.3503 (UTC) X-MS-Exchange-CrossTenant-Network-Message-Id: c9425e16-5add-4168-d02b-08dd38c53de4 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: SY1PEPF000066C2.ausprd01.prod.outlook.com X-MS-Exchange-CrossTenant-AuthAs: Anonymous X-MS-Exchange-CrossTenant-FromEntityHeader: HybridOnPrem X-MS-Exchange-Transport-CrossTenantHeadersStamped: SY8P300MB0064 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SY7P300MB0761B08085ED0691EBA1B78DA1E42SY7P300MB0761AUSP_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Thanks for the replies, appreciated... My current solution is: select trip_code, station_no, timer_sec + interval '12 hour' as NZST, timer_sec as utc, hstore_to_json(string_agg(values_sec::text, ', ')::hstore) as v= alues_sec from (select '$TRIP' as trip_code, $STATION as station_no, date_trunc('second', timer) as timer_sec, values_sec from t_reading_hstore_sec where timer >=3D '$ISO_S'::timestamp - interval '12 hour' and timer <=3D '$ISO_F'::timestamp - interval '12 hour') as fo= o group by timer_sec, trip_code, station_no; Convert the hstore to text, aggregate the text with string_agg(), convert b= ack to hstore (which seems to remove duplicate keys, OK for my purpose) and group by timer truncated to whole seconds. I also provide UTC & local t= imezone times for each set of readings. It is run in a bash script which pa= sses the trip & station values to the query, as well as the start/finish ti= mes as ISO format strings. The output is going to a Sqlite3 (Spatialite) database, which does not have= hstore, or all the hstore functionality that Postgres has, but does have a= json datatype which is adequate for our purposes, hence the hstore_to_json= in the query. Thanks again, Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 ________________________________ From: David G. Johnston Sent: Monday, January 20, 2025 04:28 To: Brent Wood Cc: pgsql-general@lists.postgresql.org Subject: concatenating hstores in a group by? On Friday, January 17, 2025, Brent Wood > wrote: I want to concatenate the hstores, There are no hstore aggregate functions. You=92ll want to convert them to,= json first then use the json_objectagg aggregate function. David J. [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_SY7P300MB0761B08085ED0691EBA1B78DA1E42SY7P300MB0761AUSP_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
Thanks for the replies, appreciated...

My current solution is:

select trip_code,
            station_no,
            timer_sec + interval '12 hour'= as NZST,
            timer_sec as utc,
            hstore_to_json(string_agg(valu= es_sec::text, ', ')::hstore) as values_sec
     from (select '$TRIP' as trip_code,
                  $STATION = as station_no,
                  date_trun= c('second', timer) as timer_sec,
                  values_se= c
           from t_reading_hstore_sec
           where timer >=3D '$ISO_S'::t= imestamp - interval '12 hour'
             and timer <=3D '$ISO_= F'::timestamp - interval '12 hour') as foo
group by timer_sec, trip_code, station_no;

Convert the hstore to text, aggregate the text with string_agg(), convert b= ack to hstore (which seems to remove duplicate keys, OK for my purpose)&nbs= p;
and group by timer truncated to whole seconds. I also provide UTC & loc= al timezone times for each set of readings. It is run in a bash script whic= h passes the trip & station values to the query, as well as the start/f= inish times as ISO format strings.

The output is going to a Sqlite3 (Spatialite) database, which does not have= hstore, or all the hstore functionality that Postgres has, but does have a= json datatype which is adequate for our purposes, hence the hstore_to_json= in the query.


Thanks again,

Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529
From: David G. Johnston <= ;david.g.johnston@gmail.com>
Sent: Monday, January 20, 2025 04:28
To: Brent Wood <Brent.Wood@niwa.co.nz>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postg= resql.org>
Subject: concatenating hstores in a group by?
 
On Friday, January 17, 2025, Brent Wood <Brent.Wood@niwa.co.nz> wrote:

I want to concatenate the hstores,


There are no hstore aggregate functions.  You=92ll want to conver= t them to,json first then use the json_objectagg aggregate function.
 
David J.

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_SY7P300MB0761B08085ED0691EBA1B78DA1E42SY7P300MB0761AUSP_--