Date: Fri, 3 Oct 2003 09:33:35 +0300 (EEST)
From: achill@matrix.gatewaynet.com
To: David B <postgresql@thegatelys.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to figure out when was a table created
In-Reply-To: <GOEGKICPFOPNLIEIHGFJEEJOCBAA.postgresql@thegatelys.com>
Message-ID: <Pine.LNX.4.44.0310030923580.5168-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


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
> -D
> 
> p.s. Love this forum!
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>