Re: GENERATE AS

[Ron <ronljohnsonjr@xxxxxxxxx>]

    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.