From: | "Wetmore, Matthew (CTR)" <Matthew(dot)Wetmore(at)express-scripts(dot)com> |
---|---|
To: | "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | GENERATE AS |
Date: | 2023-06-22 20:00:31 |
Message-ID: | 7c48174f8ce449af8f3e875bdeca7150@express-scripts.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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 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
add column matt_time timestamp with time zone default current_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', matt_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('hour', current_timestamp)::INT - date_part('hour', matt_time)::INT) stored
ERROR: generation expression is not immutable
SQL state: 42P17
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2023-06-22 20:08:38 | Re: GENERATE AS |
Previous Message | Wetmore, Matthew (CTR) | 2023-06-22 16:49:47 | work_mem + refresh mat view concurrently performance |