Обсуждение: Datestamps

I am trying to figure out what day some things were entered into the
DB.  Is there a way to do that?

--Bryan

On Fri, Jan 09, 2004 at 11:45:17 -0800,
  Bryan Irvine <bryan.irvine@kingcountyjournal.com> wrote:
> I am trying to figure out what day some things were entered into the
> DB.  Is there a way to do that?

Rows do not have timestamps associated with them. You might be able to make
some guesses using your logfile. If this is something you want for the
future you can add a timestamp field to your table and use a trigger to
keep it up to date.

On Fri, Jan 09, 2004 at 12:25:45 -0800,
  Bryan Irvine <bryan.irvine@kingcountyjournal.com> wrote:
> On Fri, 2004-01-09 at 12:25, Bruno Wolff III wrote:
> > On Fri, Jan 09, 2004 at 11:45:17 -0800,
> >   Bryan Irvine <bryan.irvine@kingcountyjournal.com> wrote:
> > > I am trying to figure out what day some things were entered into the
> > > DB.  Is there a way to do that?
> >
> > Rows do not have timestamps associated with them. You might be able to make
> > some guesses using your logfile. If this is something you want for the
> > future you can add a timestamp field to your table and use a trigger to
> > keep it up to date.
>
> I do I do!
>
> What's the best way to create a field for tracking this?
> Is this in the FAQ somewhere?

I don't think there is an FAQ, but it has been discussed on the lists a
few times. You probably want to use a before trigger to set the timestamp
column to current_timestamp on inserts or updates.

> > > Rows do not have timestamps associated with them. You might be able to make
> > > some guesses using your logfile. If this is something you want for the
> > > future you can add a timestamp field to your table and use a trigger to
> > > keep it up to date.

uhm, *digs toe into ground* where would my logfile be?

On Fri, Jan 09, 2004 at 13:12:12 -0800,
  Bryan Irvine <bryan.irvine@kingcountyjournal.com> wrote:
> > > > Rows do not have timestamps associated with them. You might be able to make
> > > > some guesses using your logfile. If this is something you want for the
> > > > future you can add a timestamp field to your table and use a trigger to
> > > > keep it up to date.
>
> uhm, *digs toe into ground* where would my logfile be?

The logs might just be being thrown away. They might be being stored using
syslog in which case they would probably be in var/log somewhere. They
might be being saved into a file. Look at the commands used to start
the database server in this case to see where the file is.
For my use I save the logs using multilog, but I don't think any
distributor sets up postgres that way.

On Friday 09 January 2004 12:52 pm, Bruno Wolff III wrote:
> On Fri, Jan 09, 2004 at 12:25:45 -0800,
>
>   Bryan Irvine <bryan.irvine@kingcountyjournal.com> wrote:
> > On Fri, 2004-01-09 at 12:25, Bruno Wolff III wrote:
> > > On Fri, Jan 09, 2004 at 11:45:17 -0800,
> > >
> > >   Bryan Irvine <bryan.irvine@kingcountyjournal.com> wrote:
> > > > I am trying to figure out what day some things were entered
> > > > into the DB.  Is there a way to do that?
> > >
> > > Rows do not have timestamps associated with them. You might be
> > > able to make some guesses using your logfile. If this is
> > > something you want for the future you can add a timestamp field
> > > to your table and use a trigger to keep it up to date.
> >
> > I do I do!
> >
> > What's the best way to create a field for tracking this?
> > Is this in the FAQ somewhere?
>
> I don't think there is an FAQ, but it has been discussed on the
> lists a few times. You probably want to use a before trigger to set
> the timestamp column to current_timestamp on inserts or updates.

Creating a timestamp on inserts (not updates) is easy:

create table foo (....., mytimestamp timestamptz not null default
now(), ...);

Cheers,
Steve