Re: GENERATE AS

From the docs ...

age ( timestamp, timestamp ) → interval

Subtract arguments, producing a “symbolic” result that uses years and
months, rather than just days

age(timestamp '2001-04-10', timestamp '1957-06-13') → 43 years 9 mons 27
days

On Thu, Jun 22, 2023 at 1:00 PM Wetmore, Matthew (CTR) <
Matthew(dot)Wetmore(at)express-scripts(dot)com> wrote:

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

--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential
information. If you are not the intended recipient, please do not print,
copy, retransmit, disseminate or otherwise use the information. Please
indicate to the sender that you have received this email in error and
delete the copy you received. Thank You.