Message-ID: <64637.216.238.112.88.1065183149.squirrel@$HOSTNAME>
Date: Fri, 3 Oct 2003 08:12:29 -0400 (EDT)
Subject: Re: How to figure out when was a table created
From: <btober@seaworthysys.com>
To: <achill@matrix.gatewaynet.com>
In-Reply-To: <Pine.LNX.4.44.0310030923580.5168-100000@matrix.gatewaynet.com>
References: <GOEGKICPFOPNLIEIHGFJEEJOCBAA.postgresql@thegatelys.com>
	<Pine.LNX.4.44.0310030923580.5168-100000@matrix.gatewaynet.com>
Importance: Normal
Cc: <postgresql@thegatelys.com>, <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Can you add two columns to the table creation definition, one with a
default current timestamp, the second to be updated with current time in
an on update trigger. Then perhaps you could do something like

SELECT
  min(new_insert_timestamp_column),
  max(new_update_timestamp_column) FROM your_table;

to get the table-wide date of first entry and last update.

~Berend Tober

>
> Well, in certain filesystems you can have the birth time
> (like ufs2) stored in the inode struct.
>
> So you find the file name in your $PGDATA/base directory
> using the oid of your table (in pg_class),
> and then you open that file with stat (2) or utimes (2) (or
> from perl) to read creation data.
>
> All that apply for FreeBSD, see if creation time is supported
> in ext2/3.
>
> On Thu, 2 Oct 2003, David B wrote:
>
>> Hi folks,
>>
>> I posted this question a few days ago and got no response so I guess
>> it cannot be done (surprising!)
>> So that leaves me with my business problem.
>>
>> We create a table for each days activity.
>> After N days (typically 7 days) we can drop the table.
>> The table name is not known so cannot force business to make
>> tablename something like mydata_MMDDYY
>>
>> I'd like to be able to do something like:
>> 	SELECT tablename
>> 	FROM   pg_???
>> 	WHERE  to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7
>> days',
>> 'dd/mm/yy' )
>>
>> Any suggestions?
>>
>> --- Prior msg was:
>>
>> Folks,
>>
>> I have a list of tables for which I want to get the date they were
>> created...and if possible the date last updateded.
>>
>> I suspect there is a pg_??? table that can answer this question but I
>> don't know what it is and I cannot find it mentioned in any docs.
>>
>> Any suggestions...tia