Re: GENERATE AS

On 6/22/23 15:45, Ron wrote:

On 6/22/23 15:00, Wetmore, Matthew (CTR) wrote:

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}div.WordSection1 {page:WordSection1;}

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)