Timestamp of insertion of the row.

am 11.06.2003 11:12:42 von Anagha Joshi

This is a multi-part message in MIME format.

------_=_NextPart_001_01C32FF9.9D260552
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hi,
Is there any way to know data & time when the row is inserted into a
table?

Help appreciated.
Thx,
Anagha

------_=_NextPart_001_01C32FF9.9D260552
Content-Type: text/html;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Timestamp of insertion of the row.

Hi, T>

Is there=
any way to know data & time when the row is inserted N LANG=3D"en-us">=
into a table >?<=
SPAN LANG=3D"en-us">

Help app=
reciated.

Thx, NT>

Anagha FONT>

------_=_NextPart_001_01C32FF9.9D260552--

Re: Timestamp of insertion of the row.

am 11.06.2003 11:32:12 von Peter Galbavy

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Timestamp of insertion of the row.

am 11.06.2003 11:58:35 von Gaetano Mendola

"Anagha Joshi" 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Timestamp of insertion of the row.

am 11.06.2003 16:16:08 von bhuvan

> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Timestamp of insertion of the row.

am 12.06.2003 14:00:57 von Henry House

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
for information. My OpenPGP key: .

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Timestamp of insertion of the row.

am 13.06.2003 00:17:38 von Gaetano Mendola

"Henry House" 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Timestamp of insertion of the row.

am 13.06.2003 17:39:35 von Bruno Wolff III

On Fri, Jun 13, 2003 at 00:17:38 +0200,
Mendola Gaetano wrote:
> "Henry House" 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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Timestamp of insertion of the row.

am 16.06.2003 10:34:46 von Gaetano Mendola

"Bruno Wolff III" 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: Timestamp of insertion of the row.

am 16.06.2003 12:07:13 von Bruno Wolff III

On Mon, Jun 16, 2003 at 10:34:46 +0200,
Mendola Gaetano wrote:
> "Bruno Wolff III" 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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: Timestamp of insertion of the row.

am 16.06.2003 12:12:10 von Gaetano Mendola

"Bruno Wolff III" wrote:
> Mendola Gaetano wrote:
> > "Bruno Wolff III" 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Timestamp of insertion of the row.

am 16.06.2003 12:15:28 von Anagha Joshi

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]=20
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" 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,=20
not the time of the transaction inside where the row was inserted.

Regards
Gaetano Mendola=20

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Timestamp of insertion of the row.

am 16.06.2003 12:56:23 von Gaetano Mendola

"Anagha Joshi" 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Timestamp of insertion of the row.

am 16.06.2003 13:46:42 von Bruno Wolff III

On Mon, Jun 16, 2003 at 15:45:28 +0530,
Anagha Joshi 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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend