From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: GENERATE AS
Date: Thu, 22 Jun 2023 15:54:54 -0500
Message-ID: <8ed2489b-7198-dbe7-c86f-ef7fee3af2a0@gmail.com> (raw)
In-Reply-To: <328b8fb1-08e8-2c74-a2b8-878ad5e9f9a4@gmail.com>
References: <7c48174f8ce449af8f3e875bdeca7150@express-scripts.com>
<328b8fb1-08e8-2c74-a2b8-878ad5e9f9a4@gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: GENERATE AS
In-Reply-To: <8ed2489b-7198-dbe7-c86f-ef7fee3af2a0@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox