Thread: Timestamp of insertion of the row.
Hi,
Is there any way to know data & time when the row is inserted into a table?
Help appreciated.
Thx,
Anagha
Anagha Joshi wrote: > Hi, > Is there any way to know data & time when the row is inserted into a > table? A timestamp column with a default value of 'now' ??? This will only work for inserts and not updates. Peter
"Anagha Joshi" <ajoshi@nulinkinc.com> wrote: > Hi, > Is there any way to know data & time when the row is inserted into a > table? You should define a supplementary field with default value "now()" this field will contain the transaction start time, if you want have the real time (if you insert a milion of row inside the same transaction using now() you'll have the same timestamp ) you should use instead: "timeofday()". Regards Gaetano Mendola
> Hi, > Is there any way to know data & time when the row is inserted into a > table? Add a timestamp field and set the default value as now(). If you wish to maintain similar value during update, a before trigger could help. regards, bhuvaneswaran
On Wed, Jun 11, 2003 at 02:42:42PM +0530, Anagha Joshi wrote:
> Hi,
> Is there any way to know data & time when the row is inserted into a
> table?
Yes. Easy answer: use a column of type 'timestamp default now()'. Whenever
row is inserted with the value for that columns unspecified, it will take on
the current timestamp. Harder answer: write a function to update the
timestamp columns and run before update or insert as a trigger on the table
in question. Here is working example:
CREATE TABLE "example" (
"id" integer DEFAULT nextval('"example_id_seq"'::text) NOT NULL,
"descr" text,
"mod" timestamp with time zone DEFAULT now(),
"last_user" text,
Constraint "example_pkey" Primary Key ("id")
);
CREATE OR REPLACE FUNCTION "update_example_timestamp" () RETURNS opaque AS '
BEGIN
-- Remember who last changed the row and when
NEW.mod := ''now'';
NEW.last_user := current_user;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER "example_on_update_set_timestamp" BEFORE INSERT OR UPDATE ON "example" FOR EACH ROW EXECUTE PROCEDURE
"update_example_timestamp"();
This also logs the last user to modify the row. This system provides only
rudimentary accountability; a more rigorous solution would be to log all
inserts and updates to a row in another table example_log with columns for
example id, timestamp, and user.
PS. On most public lists, HTML e-mail is considered improper. It will also
cause people who filter HTML e-mail as spam to likely not see your messages.
I recommend sending plain text e-mail only to public mailing lists.
--
Henry House
The attached file is a digital signature. See <http://romana.hajhouse.org/pgp>
for information. My OpenPGP key: <http://romana.hajhouse.org/hajhouse.asc>.
"Henry House" <hajhouse@houseag.uce-not-wanted-here.com> wrote: > Yes. Easy answer: use a column of type 'timestamp default now()'. With that default value you store the time stamp of transaction where the row was inserted. Immagine to insert inside the same transaction a lot of rows and this operation will take long 1 minute, you'll have all rows with the same time stamp instead of time stamp spreaded inside that minute, use timeofday instead. Regards Gaetano Mendola
On Fri, Jun 13, 2003 at 00:17:38 +0200, Mendola Gaetano <mendola@bigfoot.com> wrote: > "Henry House" <hajhouse@houseag.uce-not-wanted-here.com> wrote: > > Yes. Easy answer: use a column of type 'timestamp default now()'. > > With that default value you store the time > stamp of transaction where the row was inserted. Immagine to insert > inside the same transaction a lot of rows and this operation will take long > 1 minute, you'll have all rows with the same time stamp instead of time > stamp spreaded inside that minute, use timeofday instead. You still may not want to use timeofday even for long transactions. It depends on what the data really means to you.
"Bruno Wolff III" <bruno@wolff.to> wrote: > You still may not want to use timeofday even for long transactions. > It depends on what the data really means to you. The OP was looking for a way to know the time of a row insertion, not the time of the transaction inside where the row was inserted. Regards Gaetano Mendola
On Mon, Jun 16, 2003 at 10:34:46 +0200, Mendola Gaetano <mendola@bigfoot.com> wrote: > "Bruno Wolff III" <bruno@wolff.to> wrote: > > You still may not want to use timeofday even for long transactions. > > It depends on what the data really means to you. > > The OP was looking for a way to know the time of a row insertion, > not the time of the transaction inside where the row was inserted. And what exactly does that mean? My point was that there are a number of different things this could mean. Once that question is answered then it is possibl to give more precise solutions to the problem.
"Bruno Wolff III" <bruno@wolff.to> wrote: > Mendola Gaetano <mendola@bigfoot.com> wrote: > > "Bruno Wolff III" <bruno@wolff.to> wrote: > > > You still may not want to use timeofday even for long transactions. > > > It depends on what the data really means to you. > > > > The OP was looking for a way to know the time of a row insertion, > > not the time of the transaction inside where the row was inserted. > > And what exactly does that mean? My point was that there are a number > of different things this could mean. Once that question is answered > then it is possibl to give more precise solutions to the problem. I totally agree with you. Gaetano
Hi All, I mean to say .... Can I know the time when particular row is inserted into the table? i.e. timestamp of insertion of row into the table. Pls. note this. Thx, Anagha -----Original Message----- From: Mendola Gaetano [mailto:mendola@bigfoot.com] Sent: Monday, June 16, 2003 2:05 PM To: Bruno Wolff III Cc: Henry House; Anagha Joshi; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Timestamp of insertion of the row. "Bruno Wolff III" <bruno@wolff.to> wrote: > You still may not want to use timeofday even for long transactions. > It depends on what the data really means to you. The OP was looking for a way to know the time of a row insertion, not the time of the transaction inside where the row was inserted. Regards Gaetano Mendola
"Anagha Joshi" <ajoshi@nulinkinc.com> wrote: > Hi All, > I mean to say .... > Can I know the time when particular row is inserted into the table? i.e. > timestamp of insertion of row into the table. Is not clear what you want: # begin transaction; // 10:00:00 AM [ 3 minutes of delay ( computation, others rows inserted, ... ] # insert row // 10:03:00 AM # end transaction; if you use default now() you will have on the time stamp: 10:00:00 AM if you use default timeofday() you will have on the time stamp: 10:03:00 AM regards Gaetano Mendola
On Mon, Jun 16, 2003 at 15:45:28 +0530, Anagha Joshi <ajoshi@nulinkinc.com> wrote: > Hi All, > I mean to say .... > Can I know the time when particular row is inserted into the table? i.e. > timestamp of insertion of row into the table. That isn't precise. What do you mean when you say it is inserted into the table. Do you mean when the transaction is committed or started, or perhaps the time the command to insert the row is run or perhaps the time of the external event that prompted the the insert? Sometimes these times can be quite different, especially if you are using persistant connections with software that issues 'begin's for you. If the time doesn't need to be synchronized with any other times in the same transaction, then timeofday is probably your best bet.