Re: GENERATE AS

On 6/22/23 15:45, Ron wrote:
> On 6/22/23 15:00, Wetmore, Matthew (CTR) 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;
>>
>
> Besides what Ilya and David mentioned, CURRENT_TIMESTAMP is based on when
> the transaction started.  You might have wanted clock_timestamp().

Note the difference between bar_time and bar_time2 in rows 3 and 4.

foo=# alter table bar add column bar_time timestamp with time zone default
clock_timestamp();
ALTER TABLE

foo=# alter table bar add column bar_time2 timestamp with time zone default
current_timestamp;
ALTER TABLE

foo=# begin;
BEGIN
foo=*# insert into bar (f1, f2) values (3, 'jj');
INSERT 0 1
foo=*# insert into bar (f1, f2) values (4, 'kk');
INSERT 0 1
foo=*# commit;
COMMIT
foo=# select * from bar;
 f1 | f2 |           bar_time            | bar_time2
----+----+-------------------------------+-------------------------------
  1 | gg | 2023-06-22 15:48:43.509351-05 | 2023-06-22 15:50:27.983162-05
  2 | hh | 2023-06-22 15:50:38.675741-05 | 2023-06-22 15:50:38.675487-05
  3 | jj | 2023-06-22 15:50:57.886325-05 | 2023-06-22 15:50:49.599807-05
  4 | kk | 2023-06-22 15:51:10.374681-05 | 2023-06-22 15:50:49.599807-05
(4 rows)

--
Born in Arizona, moved to Babylonia.