From: btober@seaworthysys.com
To: achill@matrix.gatewaynet.com
Cc: postgresql@thegatelys.com
Cc: pgsql-sql@postgresql.org
Subject: Re: How to figure out when was a table created
Date: Fri, 3 Oct 2003 08:12:29 -0400 (EDT)
Message-ID: <64637.216.238.112.88.1065183149.squirrel@$HOSTNAME> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: btober@seaworthysys.com, achill@matrix.gatewaynet.com, postgresql@thegatelys.com
Subject: Re: How to figure out when was a table created
In-Reply-To: <64637.216.238.112.88.1065183149.squirrel@$HOSTNAME>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox