Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qCQV1-0003RQ-Ua for pgsql-admin@arkaria.postgresql.org; Thu, 22 Jun 2023 20:01:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qCQU1-0004ru-6u for pgsql-admin@arkaria.postgresql.org; Thu, 22 Jun 2023 20:00:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qCQU0-0004mc-2X for pgsql-admin@lists.postgresql.org; Thu, 22 Jun 2023 20:00:44 +0000 Received: from mx0a-000e5c14.pphosted.com ([148.163.133.209]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qCQTq-003g3p-Ai for pgsql-admin@lists.postgresql.org; Thu, 22 Jun 2023 20:00:41 +0000 Received: from pps.filterd (m0206225.ppops.net [127.0.0.1]) by mx0a-000e5c14.pphosted.com (8.17.1.19/8.17.1.19) with ESMTP id 35MImX1H024289 for ; Thu, 22 Jun 2023 15:00:32 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=express-scripts.com; h=from : to : subject : date : message-id : content-type : mime-version; s=pod140124231234; bh=MrkIWBv0W/lK36jQGEpkorl1MRav9ob9oPJWkWl1rKk=; b=vEpCAQaeNAjJddviruM8XeegBr0LJTKerG7SQkh5ofczgAfUYBJEb0TxSjZa8uRBq8mN XonTXEOTFlruYZfS3kaF9bD+wXD7lfAiN3EhggqteNAvZ1MtrtIhg8S+HBwyXvcdhgf5 4tviVkLmf0LW+vAOiFDVzIPc4tCVs4g6ugQTlBP/KwdRXvuoKN1FMKy0NS+K5I9g/UmE PC6XWUKVgzRoL1guMLXcv5uyBMq6uitwnvY65fXoo6GtFx8lFtjSXQxK2aeidqgSm5yx VVy3wdhPQqP6kj4vwIaNxSpCEgG1Zig8zKrQOcJPqejDHQUEEQcnQ0QOKeKNIli4YYq0 Fw== Received: from eiaappxp0007.gid.prd.globalcore.com ([170.48.0.130]) by mx0a-000e5c14.pphosted.com (PPS) with ESMTPS id 3rcrt3gr5p-7 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Thu, 22 Jun 2023 15:00:32 -0500 Received: from cvlappxp20748.internal.cigna.com (HELO PS2PW1012188.accounts.root.corp) ([10.36.52.78]) by EIAAPPXP0007.gid.prd.globalcore.com with ESMTP/TLS/ECDHE-RSA-AES256-GCM-SHA384; 22 Jun 2023 20:00:31 +0000 Received: from PS2PW1012186.accounts.root.corp (10.221.225.154) by PS2PW1012188.accounts.root.corp (10.221.225.158) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.1118.26; Thu, 22 Jun 2023 16:00:31 -0400 Received: from PS2PW1012186.accounts.root.corp ([fe80::9fec:fffb:1381:33ad]) by PS2PW1012186.accounts.root.corp ([fe80::9fec:fffb:1381:33ad%6]) with mapi id 15.02.1118.026; Thu, 22 Jun 2023 16:00:31 -0400 From: "Wetmore, Matthew (CTR)" To: "pgsql-admin@lists.postgresql.org" Subject: GENERATE AS Thread-Topic: GENERATE AS Thread-Index: AdmlQrLyTyHk5PZ/Rt+vdgjgyKHjnQ== Date: Thu, 22 Jun 2023 20:00:31 +0000 Message-ID: <7c48174f8ce449af8f3e875bdeca7150@express-scripts.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.222.252.1] x-proofpoint-routing: 000e5c14 Content-Type: multipart/alternative; boundary="_000_7c48174f8ce449af8f3e875bdeca7150expressscriptscom_" MIME-Version: 1.0 X-CFilter-Loop: Reflected X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.254,Aquarius:18.0.957,Hydra:6.0.591,FMLib:17.11.176.26 definitions=2023-06-22_15,2023-06-22_02,2023-05-22_02 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_7c48174f8ce449af8f3e875bdeca7150expressscriptscom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I have this issue and now I'm just wasting time. Can you tell me what I= 'm doing wrong? I'd like to subtract a column timestamp hour from current hour to give me h= ours elapased. How do I do this easily? I think I've tried every combination of types and casting. Thanks in advance. --------------------------- 1. ALTER TABLE matt add column matt_time timestamp with time zone default curren= t_timestamp; 2. select matt_time FROM matt; 2023-06-22 14:31:16.548622-04 timestamp with time zone 3. Select (date_part('hour', current_timestamp)::INT - date_part('hour', ma= tt_time)::INT) FROM matt 0 (same hour, so 0 is OK) INT 4. ALTER TABLE auto_auth.matt ADD column matt_hour INT GENERATED ALWAYS AS (date_part('ho= ur', current_timestamp)::INT - date_part('hour', matt_time)::INT) stored ERROR: generation expression is not immutable SQL state: 42P17 --_000_7c48174f8ce449af8f3e875bdeca7150expressscriptscom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi, I have this issue and now I’m just wasting= time. Can you tell me what I’m doing wrong?

 

I’d like to subtract a column timestamp hour f= rom current hour to give me hours elapased.

 

How do I do this easily?

 

I think I’ve tried every combination of types = and casting.

 

Thanks in advance.

---------------------------

 

1. ALTER TABLE matt

        &nbs= p;      add column matt_time timestamp with time z= one default current_timestamp;

 

2. select matt_time FROM matt;

 

2023-06-22 14:31:16.548622-04 timestamp with time zo= ne

 

3. Select (date_part('hour', current_timestamp)::INT= - date_part('hour', matt_time)::INT) FROM matt

 

0 (same hour, so 0 is OK) INT

 

4. ALTER TABLE auto_auth.matt

        &nbs= p;      ADD column matt_hour INT  GENERATED A= LWAYS AS (date_part('hour', current_timestamp)::INT - date_part('hour', mat= t_time)::INT) stored       

        &nbs= p;     

        &nbs= p;      ERROR:  generation expression is not = immutable

SQL state: 42P17

 

 

--_000_7c48174f8ce449af8f3e875bdeca7150expressscriptscom_--