Thread: Timestamp of insertion of the row.

Timestamp of insertion of the row.

From
"Anagha Joshi"
Date:

Hi,

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

Help appreciated.

Thx,

Anagha

Re: Timestamp of insertion of the row.

From
"Peter Galbavy"
Date:
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

Re: Timestamp of insertion of the row.

From
"Mendola Gaetano"
Date:
"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


Re: Timestamp of insertion of the row.

From
"A.Bhuvaneswaran"
Date:
> 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


Re: Timestamp of insertion of the row.

From
Henry House
Date:
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>.

Re: Timestamp of insertion of the row.

From
"Mendola Gaetano"
Date:
"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





Re: Timestamp of insertion of the row.

From
Bruno Wolff III
Date:
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.

Re: Timestamp of insertion of the row.

From
"Mendola Gaetano"
Date:
"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


Re: Timestamp of insertion of the row.

From
Bruno Wolff III
Date:
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.

Re: Timestamp of insertion of the row.

From
"Mendola Gaetano"
Date:
"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


Re: Timestamp of insertion of the row.

From
"Anagha Joshi"
Date:
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


Re: Timestamp of insertion of the row.

From
"Mendola Gaetano"
Date:
"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





Re: Timestamp of insertion of the row.

From
Bruno Wolff III
Date:
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.